﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Windows.Forms;
using HRM.HRMServiceProxy;
using System.Xml;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Drawing;
using CustomUIControls;
using System.Globalization;
using System.IO;
using Microsoft.Reporting.WinForms;
using System.Drawing.Imaging;
using System.Drawing.Printing;
using System.ServiceModel;
using System.Text.RegularExpressions;
using HRM.Properties;

namespace HRM
{
    public class Service
    {
        public static RoleRightInfo GetRoleRight(string StrRole)
        {
            RoleRightClient wcf = Service.RoleRight();
            RoleRightInfo retVal = wcf.RoleRight_GetRoleRight(Program.strUserName, StrRole);
            wcf.Close();
            return retVal;
        }
        public static void LoadRoleRight()
        {
            UIParamater.Quyen_HoSoCanBo = Service.GetRoleRight("AA");
            UIParamater.Quyen_NgayNghiVaCongTac = Service.GetRoleRight("AB");
            UIParamater.Quyen_BangPhanCa = Service.GetRoleRight("AC");
            UIParamater.Quyen_CacDeTaiKhoaHoc = Service.GetRoleRight("AD");
            UIParamater.Quyen_CanhBaoNangLuong = Service.GetRoleRight("AF");
            UIParamater.Quyen_CanhBaoSapNghiHuu = Service.GetRoleRight("AG");
            UIParamater.Quyen_CanhBaoSapSinhNhat = Service.GetRoleRight("AH");
            UIParamater.Quyen_ThietBiCCLD = Service.GetRoleRight("AI");
            UIParamater.Quyen_NhanVienNghiViec = Service.GetRoleRight("AJ");
            UIParamater.Quyen_CacKhoaDaoTao = Service.GetRoleRight("AE");

            UIParamater.Quyen_BangLuong = Service.GetRoleRight("BA");
            UIParamater.Quyen_BangChamCong = Service.GetRoleRight("BB");
            UIParamater.Quyen_DeNghiTroCap = Service.GetRoleRight("BC");
            UIParamater.Quyen_DoiChieuQuyBHXH = Service.GetRoleRight("BD");
            UIParamater.Quyen_TamUngLuong = Service.GetRoleRight("BE");
            UIParamater.Quyen_TruyDongBHXH = Service.GetRoleRight("BF");
            UIParamater.Quyen_KhenThuong = Service.GetRoleRight("BG");
            UIParamater.Quyen_DeNghiCapSoBH = Service.GetRoleRight("BH");
            UIParamater.Quyen_DieuChinhSoBH = Service.GetRoleRight("BI");
            UIParamater.Quyen_PhatSinhBaoHiem = Service.GetRoleRight("BJ");
            UIParamater.Quyen_ImportDuLieuCQBH = Service.GetRoleRight("BK");
            UIParamater.Quyen_UNC = Service.GetRoleRight("BL");

            UIParamater.Quyen_DotTuyenDung = Service.GetRoleRight("CA");
            UIParamater.Quyen_UngVien = Service.GetRoleRight("CB");

            UIParamater.Quyen_ThamSoHeThong = Service.GetRoleRight("DA");
            UIParamater.Quyen_ChiNhanh = Service.GetRoleRight("DB");
            UIParamater.Quyen_PhongBan = Service.GetRoleRight("DC");
            UIParamater.Quyen_ChucVu = Service.GetRoleRight("DD");
            UIParamater.Quyen_CapDo = Service.GetRoleRight("DE");
            UIParamater.Quyen_NgoaiNgu = Service.GetRoleRight("DF");
            UIParamater.Quyen_DanToc = Service.GetRoleRight("DG");
            UIParamater.Quyen_TonGiao = Service.GetRoleRight("DH");
            UIParamater.Quyen_Bac = Service.GetRoleRight("DI");
            UIParamater.Quyen_NgachBac = Service.GetRoleRight("DJ");
            UIParamater.Quyen_TinhTrangHonNhan = Service.GetRoleRight("DK");
            UIParamater.Quyen_LoaiHopDong = Service.GetRoleRight("DL");
            UIParamater.Quyen_HinhThucNghi = Service.GetRoleRight("DM");
            UIParamater.Quyen_LaiSuatChamNop = Service.GetRoleRight("DO");
            UIParamater.Quyen_NgayNghiTrongNam = Service.GetRoleRight("DP");
            UIParamater.Quyen_TieuChiDanhGia = Service.GetRoleRight("DQ");
            UIParamater.Quyen_TrinhDo = Service.GetRoleRight("DR");
            UIParamater.Quyen_HinhThucNghiViec = Service.GetRoleRight("DS");
            UIParamater.Quyen_QuocGia = Service.GetRoleRight("DT");
            UIParamater.Quyen_TinhThanhPho = Service.GetRoleRight("DU");
            UIParamater.Quyen_NoiKhamChuaBenh = Service.GetRoleRight("DV");
            UIParamater.Quyen_TyLeThuBaoHiem = Service.GetRoleRight("DX");
            UIParamater.Quyen_NghiepVuBaoHiem = Service.GetRoleRight("DY");
            UIParamater.Quyen_MucLuongToiThieu = Service.GetRoleRight("DZ");

            UIParamater.Quyen_BaoCaoNhanSu = Service.GetRoleRight("EA");
            UIParamater.Quyen_BaoCaoTuyenDung = Service.GetRoleRight("EB");
            UIParamater.Quyen_BaoCaoBHXH = Service.GetRoleRight("EC");
            UIParamater.Quyen_BaoCaoHuongTroCap = Service.GetRoleRight("ED");
            UIParamater.Quyen_CaLamViec = Service.GetRoleRight("DN");
        }
        public static void LoadSystem()
        {
            UserClient _wcfUser = Service.User();
            BranchClient _wcfBranch = Service.Branch();
            BranchInfo _BranchInfo = new BranchInfo();
            DepartmentClient _wcfDepartment = Service.Department();
            ParameterClient _wcfParameter = Service.Parameter();
            EmployeeClient _wcfEmpl = Service.Employee();

            Program.isAdmin = _wcfUser.User_IsUserInRole(Program.strUserName, "hrm");
            Program.isViewAll = _wcfUser.User_IsUserInRole(Program.strUserName, "hrm_viewall");
            Program.isChuyenQuan = _wcfUser.User_IsUserInRole(Program.strUserName, "hrm_chuyenquan");

            UIParamater.CompanyName = _wcfParameter.Parameter_GetByName("CompanyName").Value;
            //DataTable dtUser = _wcfUser.User_GetInfo(Program.strUserName).Tables[0];
            //UIParamater.FullNameOfUser = dtUser.Rows[0]["FullName"].ToString();
            //Program.intBranchID = Convert.ToInt32(dtUser.Rows[0]["BranchID"].ToString());
            DataTable dt = new DataTable();
            //if (Program.isAdmin)
            //    Program.intBranchID = 0;
            if (Program.intBranchID > 0)
            {
                _BranchInfo = _wcfBranch.Branch_GetInfo(Program.intBranchID);
                try
                {
                    Program.strBranch = _BranchInfo.Name;
                    Program.strBranchAddress = _BranchInfo.Address;
                    Program.strBranchPhone = _BranchInfo.Phone;
                    Program.strBranchFax = _BranchInfo.Fax;
                    Program.strBranchRepName = _BranchInfo.RepName;
                    Program.strBranchRepPos = _BranchInfo.RepPos;
                    Program.strBranchCode = _BranchInfo.Code;
                    Program.strBranchBHXH = _BranchInfo.BHXH;
                    Program.strInsurancePlace = _BranchInfo.InsurancePlace;
                    Program.strInsuranceLevel = _BranchInfo.ManagementLevel;
                    Program.strRegionCode = _BranchInfo.RegionCode;
                    Program.intInsType = _BranchInfo.InsType;
                    
                    Program.strBranchEmail = _BranchInfo.Email;
                    Program.strBranchBankAccount = _BranchInfo.Branch_BankAccount;
                    Program.strBranchBankName = _BranchInfo.Branch_BankName;
                }
                catch (Exception ex)
                {
                }
            }
            LocalDB.dtTableEmployee = _wcfEmpl.Employee_GetAllWithBranch(Program.intBranchID).Tables[0];
            if (Program.isAdmin || Program.isViewAll)
            {
                dt = _wcfEmpl.Employee_Enumerate(0).Tables[0];
                LocalDB.dtBranch = _wcfBranch.Branch_GetAll().Tables[0];
                LocalDB.dtDepartment = _wcfDepartment.Department_GetAll().Tables[0];
            }
            else
            {
                dt = _wcfEmpl.Employee_Enumerate(Program.intBranchID).Tables[0];
                LocalDB.dtBranch = _wcfBranch.Branch_GetOne(Program.intBranchID).Tables[0];
                LocalDB.dtDepartment = _wcfDepartment.Department_GetAllWithBranch(Program.intBranchID).Tables[0];
            }

            if (dt.Rows.Count > 0)
            {
                Program.TotalEmployee = Convert.ToInt32(dt.Rows[0]["TotalEmpl"]);
                Program.TotalIncreaseSalary = Convert.ToInt32(dt.Rows[0]["IncreaseSalary"]);
                Program.TotalUpdatePos = Convert.ToInt32(dt.Rows[0]["UpdatePos"]);
                Program.TotalProbation = Convert.ToInt32(dt.Rows[0]["TotalProbation"]);
            }

        }
        public static void LoadEmployeeQuantity()
        {
            EmployeeClient _wsEmployee = Service.Employee();
            int countRow = 0;
            int _Total = 0;
            int BranchID = 0;
            DataTable dtEmployee = new DataTable();
            if (Program.isAdmin || Program.isViewAll)
                BranchID = 0;
            else
                BranchID = Program.intBranchID;
            dtEmployee = _wsEmployee.Employee_GetLabourContract(out countRow, out _Total, 0, DateTime.Now, 0, "", "", 0, 0, BranchID, 0).Tables[0];



        }
        public static HeaderClient Header()
        {
            HeaderClient wcf = new HeaderClient(ProxyConfig.GetBinding(), ProxyConfig.Header);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static DetailClient Detail()
        {
            DetailClient wcf = new DetailClient(ProxyConfig.GetBinding(), ProxyConfig.Detail);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LevelClient Level()
        {
            LevelClient wcf = new LevelClient(ProxyConfig.GetBinding(), ProxyConfig.Level);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static StandardClient Standard()
        {
            StandardClient wcf = new StandardClient(ProxyConfig.GetBinding(), ProxyConfig.Standard);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static TypeOfLeaveWorkClient TypeOfLeaveWork()
        {
            TypeOfLeaveWorkClient wcf = new TypeOfLeaveWorkClient(ProxyConfig.GetBinding(), ProxyConfig.TypeOfLeaveWork);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static IssueInsBookClient IssueInsBook()
        {
            IssueInsBookClient wcf = new IssueInsBookClient(ProxyConfig.GetBinding(), ProxyConfig.IssueInsBook);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static EditInsBookClient EditInsBook()
        {
            EditInsBookClient wcf = new EditInsBookClient(ProxyConfig.GetBinding(), ProxyConfig.EditInsBook);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }

        public static DoiSoClient DoiSo()
        {
            DoiSoClient wcf = new DoiSoClient(ProxyConfig.GetBinding(), ProxyConfig.DoiSo);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }

        public static GradeDetailClient GradeDetail()
        {
            GradeDetailClient wcf = new GradeDetailClient(ProxyConfig.GetBinding(), ProxyConfig.GradeDetail);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static SalaryAllowanceClient SalaryAllowance()
        {
            SalaryAllowanceClient wcf = new SalaryAllowanceClient(ProxyConfig.GetBinding(), ProxyConfig.SalaryAllowance);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static SafetyDeviceClient SafetyDevice()
        {
            SafetyDeviceClient wcf = new SafetyDeviceClient(ProxyConfig.GetBinding(), ProxyConfig.SafetyDevice);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LeaveRecordClient LeaveRecord()
        {
            LeaveRecordClient wcf = new LeaveRecordClient(ProxyConfig.GetBinding(), ProxyConfig.LeaveRecord);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static AdvancePaymentClient AdvancePayment()
        {
            AdvancePaymentClient wcf = new AdvancePaymentClient(ProxyConfig.GetBinding(), ProxyConfig.AdvancePayment);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static ServiceChargeClient ServiceCharge()
        {
            ServiceChargeClient wcf = new ServiceChargeClient(ProxyConfig.GetBinding(), ProxyConfig.ServiceCharge);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static SCDClient SCD()
        {
            SCDClient wcf = new SCDClient(ProxyConfig.GetBinding(), ProxyConfig.SCD);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static AwardClient Award()
        {
            AwardClient wcf = new AwardClient(ProxyConfig.GetBinding(), ProxyConfig.Award);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static BranchClient Branch()
        {
            BranchClient wcf = new BranchClient(ProxyConfig.GetBinding(), ProxyConfig.Branch);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CandidateClient Candidate()
        {
            CandidateClient wcf = new CandidateClient(ProxyConfig.GetBinding(), ProxyConfig.Candidate);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static EvaluationCriteriaClient EvaluationCriteria()
        {
            EvaluationCriteriaClient wcf = new EvaluationCriteriaClient(ProxyConfig.GetBinding(), ProxyConfig.EvaluationCriteria);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }

        public static CCClient CC()
        {
            CCClient wcf = new CCClient(ProxyConfig.GetBinding(), ProxyConfig.CC);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CRClient CR()
        {
            CRClient wcf = new CRClient(ProxyConfig.GetBinding(), ProxyConfig.CR);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CWBClient CWB()
        {
            CWBClient wcf = new CWBClient(ProxyConfig.GetBinding(), ProxyConfig.CWB);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CFamilyClient CFamily()
        {
            CFamilyClient wcf = new CFamilyClient(ProxyConfig.GetBinding(), ProxyConfig.CFamily);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CLanguageClient CLanguage()
        {
            CLanguageClient wcf = new CLanguageClient(ProxyConfig.GetBinding(), ProxyConfig.CLanguage);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CCriteriaClient CCriteria()
        {
            CCriteriaClient wcf = new CCriteriaClient(ProxyConfig.GetBinding(), ProxyConfig.CCriteria);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CInterviewClient CInterview()
        {
            CInterviewClient wcf = new CInterviewClient(ProxyConfig.GetBinding(), ProxyConfig.CInterview);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CertificateClient Certificate()
        {
            CertificateClient wcf = new CertificateClient(ProxyConfig.GetBinding(), ProxyConfig.Certificate);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CheckTimeClient CheckTime()
        {
            CheckTimeClient wcf = new CheckTimeClient(ProxyConfig.GetBinding(), ProxyConfig.CheckTime);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static CommentClient Comment()
        {
            CommentClient wcf = new CommentClient(ProxyConfig.GetBinding(), ProxyConfig.Comment);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static ContractTypeClient ContractType()
        {
            ContractTypeClient wcf = new ContractTypeClient(ProxyConfig.GetBinding(), ProxyConfig.ContractType);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static DepartmentClient Department()
        {
            DepartmentClient wcf = new DepartmentClient(ProxyConfig.GetBinding(), ProxyConfig.Department);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static EducationClient Education()
        {
            EducationClient wcf = new EducationClient(ProxyConfig.GetBinding(), ProxyConfig.Education);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static EmployeeClient Employee()
        {
            EmployeeClient wcf = new EmployeeClient(ProxyConfig.GetBinding(), ProxyConfig.Employee);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static MarkWorkClient MarkWork()
        {
            MarkWorkClient wcf = new MarkWorkClient(ProxyConfig.GetBinding(), ProxyConfig.MarkWork);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static MainServiceClient MainService()
        {
            MainServiceClient wcf = new MainServiceClient(ProxyConfig.GetBinding(), ProxyConfig.MainService);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }

        public static EmployeeShiftClient EmployeeShift()
        {
            EmployeeShiftClient wcf = new EmployeeShiftClient(ProxyConfig.GetBinding(), ProxyConfig.EmployeeShift);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static EthnicClient Ethnic()
        {
            EthnicClient wcf = new EthnicClient(ProxyConfig.GetBinding(), ProxyConfig.Ethnic);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static FamilyClient Family()
        {
            FamilyClient wcf = new FamilyClient(ProxyConfig.GetBinding(), ProxyConfig.Family);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static GradeClient Grade()
        {
            GradeClient wcf = new GradeClient(ProxyConfig.GetBinding(), ProxyConfig.Grade);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static HolidayClient Holiday()
        {
            HolidayClient wcf = new HolidayClient(ProxyConfig.GetBinding(), ProxyConfig.Holiday);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static InterestRateClient InterestRate()
        {
            InterestRateClient wcf = new InterestRateClient(ProxyConfig.GetBinding(), ProxyConfig.InterestRate);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LabourContractClient LabourContract()
        {
            LabourContractClient wcf = new LabourContractClient(ProxyConfig.GetBinding(), ProxyConfig.LabourContract);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LabourLanguageClient LabourLanguage()
        {
            LabourLanguageClient wcf = new LabourLanguageClient(ProxyConfig.GetBinding(), ProxyConfig.LabourLanguage);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LanguageClient Language()
        {
            LanguageClient wcf = new LanguageClient(ProxyConfig.GetBinding(), ProxyConfig.Language);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LaterInsuranceClient LaterInsurance()
        {
            LaterInsuranceClient wcf = new LaterInsuranceClient(ProxyConfig.GetBinding(), ProxyConfig.LaterInsurance);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LeaveClient Leave()
        {


            LeaveClient wcf = new LeaveClient(ProxyConfig.GetBinding(), ProxyConfig.Leave);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static LeaveWorkClient LeaveWork()
        {
            LeaveWorkClient wcf = new LeaveWorkClient(ProxyConfig.GetBinding(), ProxyConfig.LeaveWork);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static MaritalStatusClient MaritalStatus()
        {
            MaritalStatusClient wcf = new MaritalStatusClient(ProxyConfig.GetBinding(), ProxyConfig.MaritalStatus);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static ParameterClient Parameter()
        {
            ParameterClient wcf = new ParameterClient(ProxyConfig.GetBinding(), ProxyConfig.Parameter);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static PensionerClient Pensioner()
        {
            PensionerClient wcf = new PensionerClient(ProxyConfig.GetBinding(), ProxyConfig.Pensioner);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static PositionClient Position()
        {
            PositionClient wcf = new PositionClient(ProxyConfig.GetBinding(), ProxyConfig.Position);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static PositionDetailClient PositionDetail()
        {
            PositionDetailClient wcf = new PositionDetailClient(ProxyConfig.GetBinding(), ProxyConfig.PositionDetail);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static PremiumClient Premium()
        {
            PremiumClient wcf = new PremiumClient(ProxyConfig.GetBinding(), ProxyConfig.Premium);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static RecruitmentClient Recruitment()
        {
            RecruitmentClient wcf = new RecruitmentClient(ProxyConfig.GetBinding(), ProxyConfig.Recruitment);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static ReligionClient Religion()
        {
            ReligionClient wcf = new ReligionClient(ProxyConfig.GetBinding(), ProxyConfig.Religion);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static RoleRightClient RoleRight()
        {
            RoleRightClient wcf = new RoleRightClient(ProxyConfig.GetBinding(), ProxyConfig.RoleRight);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static SalaryClient Salary()
        {
            SalaryClient wcf = new SalaryClient(ProxyConfig.GetBinding(), ProxyConfig.Salary);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static SalaryFundClient SalaryFund()
        {
            SalaryFundClient wcf = new SalaryFundClient(ProxyConfig.GetBinding(), ProxyConfig.SalaryFund);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static ScienceClient Science()
        {
            ScienceClient wcf = new ScienceClient(ProxyConfig.GetBinding(), ProxyConfig.Science);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static MisConductClient MisConduct()
        {
            MisConductClient wcf = new MisConductClient(ProxyConfig.GetBinding(), ProxyConfig.MisConduct);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static ShiftClient Shift()
        {
            ShiftClient wcf = new ShiftClient(ProxyConfig.GetBinding(), ProxyConfig.Shift);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }


        public static UserClient User()
        {
            UserClient wcf = new UserClient(ProxyConfig.GetBinding(), ProxyConfig.User);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static VisaClient Visa()
        {
            VisaClient wcf = new VisaClient(ProxyConfig.GetBinding(), ProxyConfig.Visa);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }

        //public static WorkingBackgroundClient WorkingBackground()
        //{
        //    WorkingBackgroundClient wcf = new WorkingBackgroundClient(ProxyConfig.GetBinding(), ProxyConfig.WorkingBackground);
        //    wcf.ClientCredentials.UserName.UserName = Program.strUserName;
        //    wcf.ClientCredentials.UserName.Password = Program.strPassword;
        //    return wcf;
        //}

        public static NationalClient National()
        {
            NationalClient wcf = new NationalClient(ProxyConfig.GetBinding(), ProxyConfig.National);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static TaxClient Tax()
        {
            TaxClient wcf = new TaxClient(ProxyConfig.GetBinding(), ProxyConfig.Tax);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static MtomClient Mtom()
        {
            MtomClient wcf = new MtomClient(ProxyConfig.GetBinding(), ProxyConfig.Mtom);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static RegionClient Region()
        {
            RegionClient wcf = new RegionClient(ProxyConfig.GetBinding(), ProxyConfig.Region);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static HospitalClient Hospital()
        {
            HospitalClient wcf = new HospitalClient(ProxyConfig.GetBinding(), ProxyConfig.Hospital);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static RateClient Rate()
        {
            RateClient wcf = new RateClient(ProxyConfig.GetBinding(), ProxyConfig.Rate);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static BasicSalaryClient BasicSalary()
        {
            BasicSalaryClient wcf = new BasicSalaryClient(ProxyConfig.GetBinding(), ProxyConfig.BasicSalary);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static InsTypeClient InsType()
        {
            InsTypeClient wcf = new InsTypeClient(ProxyConfig.GetBinding(), ProxyConfig.InsType);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
        public static UNCClient UNC()
        {
            UNCClient wcf = new UNCClient(ProxyConfig.GetBinding(), ProxyConfig.UNC);
            wcf.ClientCredentials.UserName.UserName = Program.strUserName;
            wcf.ClientCredentials.UserName.Password = Program.strPassword;
            return wcf;
        }
    }

    public class UISetting
    {
        public static string GetEnglishString(string VietnameseString)
        {
            string uniChars = "àáảãạâầấẩẫậăẳẵặắằèéẻẽẹêềếểễệđìíỉĩịòóỏõơọôồốổỗộờớởỡợùúủũụỳýỷỹỵưựữựứừửÀÁẢÃẠÂẦẤẨẪẬÈÉẺẼẸÊỀẾỂỄỆĐÌÍỈĨỊÒÓỎÕƠỌÔỒỐỔỖỘỜỚỞỠỢÙÚỦŨỤỲÝỶỸỴ";
            string englishChars = "aaaaaaaaaaaaaaaaaeeeeeeeeeeediiiiiooooooooooooooooouuuuuyyyyyuuuuuuuAAAAAAAAAAAEEEEEEEEEEEDIIIIIOOOOOOOOOOOOOOOOOUUUUUYYYYY";

            string retVal = String.Empty;

            if (VietnameseString == null)
                return retVal;
            int pos;
            for (int i = 0; i < VietnameseString.Length; i++)
            {
                pos = uniChars.IndexOf(VietnameseString[i].ToString());
                if (pos >= 0)
                    retVal += englishChars[pos];
                else
                    retVal += VietnameseString[i];
            }
            return retVal;
        }

        public static Stream GetEmbeddedFile(string fileName)
        {
            try
            {
                System.Reflection.Assembly a = System.Reflection.Assembly.Load("HRMReport");
                Stream str = a.GetManifestResourceStream("HRMReport." + fileName);

                if (str == null)
                    throw new Exception("Could not locate embedded resource '" + fileName + "' in assembly ");
                return str;
            }
            catch (Exception e)
            {
                throw new Exception("HRM : " + e.Message);
            }
        }

        public static bool CheckEmail(string EmailAddress)
        {
            string strPattern = "^([0-9a-zA-Z]([-.\\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\\w]*[0-9a-zA-Z]\\.)+[a-zA-Z]{2,9})$";

            EmailAddress = Convert.ToString(EmailAddress);
            if (Regex.IsMatch(EmailAddress, strPattern))
            { return true; }
            return false;
        }

        public static string ShowTime()
        {
            string _DayofWeek = "";
            switch (DateTime.Now.DayOfWeek)
            {
                case DayOfWeek.Friday:
                    _DayofWeek = UpdateUI.GetString("thusau");
                    break;
                case DayOfWeek.Monday:
                    _DayofWeek = UpdateUI.GetString("thuhai");
                    break;
                case DayOfWeek.Saturday:
                    _DayofWeek = UpdateUI.GetString("thubay");
                    break;
                case DayOfWeek.Sunday:
                    _DayofWeek = UpdateUI.GetString("chunhat");
                    break;
                case DayOfWeek.Thursday:
                    _DayofWeek = UpdateUI.GetString("thunam");
                    break;
                case DayOfWeek.Tuesday:
                    _DayofWeek = UpdateUI.GetString("thuba");
                    break;
                case DayOfWeek.Wednesday:
                    _DayofWeek = UpdateUI.GetString("thutu");
                    break;
                default:
                    break;
            }
            _DayofWeek = _DayofWeek + ", " +
                DateTime.Now.ToString("dd/MM/yyyy hh:mm tt");

            return _DayofWeek;
        }

        public static int LaySoNgayCN(int thang, int nam)
        {
            int soNgayCN = 0;
            for (int i = 0; i <= 31; i++)
            {
                try
                {
                    DateTime dt = new DateTime(nam, thang, i);
                    if (dt.DayOfWeek == DayOfWeek.Sunday)
                    {
                        soNgayCN++;
                    }
                }
                catch (Exception)
                {

                }
            }
            return soNgayCN;
        }
        public static string TinhThamNien(DateTime _date1, DateTime _date2)
        {
            string _ThamNien = "";
            DateCalculator dc = new DateCalculator(_date1, _date2);
            dc.CalculateDateDifference();
            _ThamNien = dc.Years.ToString() + " năm " + dc.Months.ToString() + " tháng " + dc.Days.ToString() + " ngày";
            return _ThamNien;
        }
        public static decimal CountWeekend(DateTime _date1, DateTime _date2)
        {
            int _count = 0;
            while (_date1 < _date2)
            {
                if (_date1.DayOfWeek == DayOfWeek.Saturday || _date1.DayOfWeek == DayOfWeek.Sunday)
                {
                    _count++;
                }
                _date1 = _date1.AddDays(1);
            }
            return _count;
        }
        public static decimal CountSunday(DateTime _date1, DateTime _date2)
        {
            int _count = 0;
            while (_date1 < _date2)
            {
                if (_date1.DayOfWeek == DayOfWeek.Sunday)
                {
                    _count++;
                }
                _date1 = _date1.AddDays(1);
            }
            return _count;
        }
        public static decimal CountHoliday(DateTime _date1, DateTime _date2)
        {
            int _count = 0;
            HolidayClient wcfHoliday = Service.Holiday();
            while (_date1 < _date2)
            {
                if (wcfHoliday.Holiday_Exist(_date1))
                {
                    _count++;
                }
                _date1 = _date1.AddDays(1);
            }
            return _count;
        }
        /// <summary>
        /// Nhận địa chỉ máy chủ, hoặc IP
        /// </summary>
        /// <returns></returns>
        public static string GetIP()
        {
            string retVal = Properties.Settings.Default.ServiceAddress;

            retVal = retVal.Substring(retVal.IndexOf("://") + 3);
            retVal = retVal.Substring(0, retVal.IndexOf("/"));
            if (retVal.Contains(":"))
                retVal = retVal.Substring(0, retVal.IndexOf(":"));

            return retVal;
        }
        /// <summary>
        /// Thay chuỗi chứa dấu (') = chuỗi mới.
        /// </summary>
        /// <param name="KeyWord">TextBoxName.Text.Trim()</param>
        /// <returns></returns>

        public static string GetKeyWord(string KeyWord)
        {
            string _keyword = "";
            if (KeyWord.Contains("'"))
                _keyword = KeyWord.Replace("'", "''");
            else
                _keyword = KeyWord;
            return _keyword;
        }

        /// <summary>
        /// Nạp thông tin cho TreeView 3 mức 
        /// </summary>
        /// <param name="tv">TreeView Name</param>
        public static void LoadTreeView3(ref tvUser tv)
        {
            //tv.BackColor = Color.White;
            try
            {
                BranchClient wcfBranch = HRM.Service.Branch();
                TreeNode node;
                DataTable dt1;

                tv.Nodes.Clear();
                node = tv.Nodes.Add("Root", UIParamater.CompanyName, 0, 0);
                node.Tag = 0;
                node.ExpandAll();
                if (!Program.isAdmin && !Program.isViewAll)
                {
                    // dt1 = wcfBranch.Branch_GetOne(Program.intBranchID).Tables[0];
                    dt1 = LocalDB.Branch_GetOne(Program.intBranchID.ToString());
                }
                else

                    //dt1 = wcfBranch.Branch_GetAll().Tables[0];                    
                    dt1 = LocalDB.Branch_GetAll();
                LoadChilld_HRMProfile(node, dt1);

                //
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadTreeViewHRMProfile(ref tvUser tv)
        {
            //tv.BackColor = Color.White;
            try
            {
                BranchClient wcfBranch = HRM.Service.Branch();
                TreeNode node;
                DataTable dt1;

                tv.Nodes.Clear();
                node = tv.Nodes.Add("Root", UIParamater.CompanyName, 0, 0);
                node.Tag = 0;
                node.ExpandAll();
                if (!Program.isAdmin && !Program.isViewAll)
                {
                    dt1 = wcfBranch.Branch_GetOne(Program.intBranchID).Tables[0];
                }
                else
                    dt1 = wcfBranch.Branch_GetAll().Tables[0];
                LoadChilldAll(node, dt1);
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        /// <summary>
        /// Nạp thông tin Đơn vị của công ty vào treeview
        /// </summary>
        /// <param name="trnode"></param>
        /// <param name="dt"></param>
        public static void LoadChilldAll(TreeNode trnode, DataTable dt)
        {
            TreeNode node1, node2;
            DataTable dt2;
            DepartmentClient wcfDepartment = HRM.Service.Department();
            foreach (DataRow dr1 in dt.Rows)
            {
                node1 = trnode.Nodes.Add(dr1["Branch_ID"].ToString(), dr1["Branch_Name"].ToString(), 1, 1);
                node1.Tag = dr1["Branch_ID"].ToString();
                //node1.ExpandAll();
                dt2 = wcfDepartment.Department_GetWithBranch(Convert.ToInt32(node1.Tag), 0).Tables[0];
                foreach (DataRow dr2 in dt2.Rows)
                {
                    node2 = node1.Nodes.Add(dr2["Department_ID"].ToString(), dr2["Department_Name"].ToString(), 2, 3);
                    node2.Tag = Convert.ToInt32(dr2["Department_ID"]);
                    //node2.ExpandAll();    
                    AddChildNode(node2);
                }
            }
        }
        public static void LoadChilld_HRMProfile(TreeNode trnode, DataTable dt)
        {
            TreeNode node1, node2;
            DataTable dt2;
            DepartmentClient wcfDepartment = HRM.Service.Department();
            foreach (DataRow dr1 in dt.Rows)
            {
                node1 = trnode.Nodes.Add(dr1["Branch_ID"].ToString(), dr1["Branch_Name"].ToString(), 1, 1);
                node1.Tag = dr1["Branch_ID"].ToString();
                if (!Program.isAdmin)
                    node1.ExpandAll();
                dt2 = wcfDepartment.Department_GetWithBranch(Convert.ToInt32(node1.Tag), 0).Tables[0];
                //dt2 = LocalDB.Department_GetAllByBranch(node1.Tag.ToString());
                foreach (DataRow dr2 in dt2.Rows)
                {
                    node2 = node1.Nodes.Add(dr2["Department_ID"].ToString(), dr2["Department_Name"].ToString(), 2, 3);
                    node2.Tag = Convert.ToInt32(dr2["Department_ID"]);
                    //node2.ExpandAll();    
                    AddChildNode(node2);
                }
            }
        }
        /// <summary>
        /// Nạp thông tin phòng ban của từng Đơn vị vào treeview
        /// </summary>
        /// <param name="node"></param>
        public static void AddChildNode(TreeNode node)
        {
            try
            {
                TreeNode cNode;
                DepartmentClient wcfDepartment = HRM.Service.Department();
                DataTable dt3 = wcfDepartment.Department_GetWitchMasterID(Convert.ToInt32(node.Tag)).Tables[0];
                //DataTable dt3 = LocalDB.Department_GetAllByMasterID(node.Tag.ToString());
                if (dt3.Rows.Count > 0)
                    foreach (DataRow dr3 in dt3.Rows)
                    {
                        cNode = node.Nodes.Add(dr3["Department_ID"].ToString(), dr3["Department_Name"].ToString(), 2, 3);
                        cNode.Tag = Convert.ToInt32(dr3["Department_ID"]);
                        AddChildNode(cNode);
                    }
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }

        /// <summary>
        /// Nạp thông tin cho TreeView 4 mức 
        /// </summary>
        /// <param name="tv">TreeView Name</param>
        public static void LoadTreeView4(ref tvUser tv)
        {
            //tv.BackColor = Color.White;
            //tv.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(210)))), ((int)(((byte)(234)))), ((int)(((byte)(246)))));
            try
            {
                BranchClient wcfBranch = Service.Branch();
                DepartmentClient wcfDepartment = Service.Department();
                EmployeeClient wcfEmployee = Service.Employee();

                TreeNode node, node1, node2, node3;
                DataTable dt1, dt2, dt3;

                tv.Nodes.Clear();
                node = tv.Nodes.Add("Root", UIParamater.CompanyName, 0, 0);
                node.Tag = 0;
                node.ExpandAll();
                if (!Program.isAdmin && !Program.isViewAll)
                {
                    dt1 = wcfBranch.Branch_GetOne(Program.intBranchID).Tables[0];
                }
                else
                    dt1 = wcfBranch.Branch_GetAll().Tables[0];
                foreach (DataRow dr1 in dt1.Rows)
                {
                    node1 = node.Nodes.Add(dr1["Branch_ID"].ToString(), dr1["Branch_Name"].ToString(), 1, 1);
                    node1.Tag = dr1["Branch_ID"].ToString();

                    dt2 = wcfDepartment.Department_GetWithBranch(Convert.ToInt32(node1.Tag), 1).Tables[0];
                    foreach (DataRow dr2 in dt2.Rows)
                    {
                        node2 = node1.Nodes.Add(dr2["Department_ID"].ToString(), dr2["Department_Name"].ToString(), 2, 3);
                        node2.Tag = dr2["Department_ID"].ToString();
                        node2.ExpandAll();

                        dt3 = wcfEmployee.Employee_GetWithDepartment(Convert.ToInt32(node2.Tag)).Tables[0];
                        foreach (DataRow dr3 in dt3.Rows)
                        {
                            node3 = node2.Nodes.Add(dr3["Employee_ID"].ToString(), dr3["FullName"].ToString(), 4, 4);
                            node3.Tag = dr3["Employee_ID"].ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }

        }

        /// <summary>
        /// Lấy ngày trong tuần
        /// </summary>
        /// <param name="_date">dd/MM/yyyy</param>
        /// <returns>DayOfWeeks</returns>
        public static string GetDayOfWeek(DateTime _date)
        {
            string _DayOfWeek = "";
            _DayOfWeek = _date.DayOfWeek.ToString();
            switch (_DayOfWeek)
            {
                case "Monday":
                    _DayOfWeek = UpdateUI.GetString("thuhai");
                    break;
                case "Tuesday":
                    _DayOfWeek = UpdateUI.GetString("thuba");
                    break;
                case "Wednesday":
                    _DayOfWeek = UpdateUI.GetString("thutu");
                    break;
                case "Thursday":
                    _DayOfWeek = UpdateUI.GetString("thunam");
                    break;
                case "Friday":
                    _DayOfWeek = UpdateUI.GetString("thusau");
                    break;
                case "Saturday":
                    _DayOfWeek = UpdateUI.GetString("thubay");
                    break;
                case "Sunday":
                    _DayOfWeek = UpdateUI.GetString("chunhat");
                    break;
            }
            return _DayOfWeek;
        }

        public static int GetIntNo(string strIntNo)
        {
            int outInt;
            if (Int32.TryParse(strIntNo, out outInt) || outInt > 0)
                return outInt;
            else
                outInt = 0;
            return outInt;
        }

        /// <summary>
        /// Chuyển đổi chuỗi  "dd/MM/yyyy" hoac "MM/yyyy" sang kiểu Datetime
        /// </summary>
        /// <param name="strDate">"dd/MM/yyyy"</param>
        /// <returns>Datetime</returns>


        public static DateTime GetDate(string sDate)
        {
            String strDate = sDate.Replace(",", "").Replace(".", "").Split(new char[]{' '})[0];
            //
            DateTime outDate;
            if (DateTime.TryParseExact(strDate, "dd/MM/yyyy", CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out outDate))
            {
                DateTime DateMin = DateTime.ParseExact("01/01/1753", "dd/MM/yyyy", CultureInfo.InvariantCulture);
                DateTime DateMax = DateTime.ParseExact("31/12/9999", "dd/MM/yyyy", CultureInfo.InvariantCulture);

                if (DateTime.Compare(outDate, DateMin) < 0 || DateTime.Compare(outDate, DateMax) > 0)
                    return DateTime.MinValue;
                else
                    return outDate;
            }

            if (DateTime.TryParseExact(strDate, "MM/yyyy", CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out outDate))
            {
                DateTime DateMin = DateTime.ParseExact("01/1753", "MM/yyyy", CultureInfo.InvariantCulture);
                DateTime DateMax = DateTime.ParseExact("12/9999", "MM/yyyy", CultureInfo.InvariantCulture);

                if (DateTime.Compare(outDate, DateMin) < 0 || DateTime.Compare(outDate, DateMax) > 0)
                    return DateTime.MinValue;
                else
                    return outDate;
            }

            if (DateTime.TryParseExact(strDate, "MM/ddd/yyyy", CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out outDate))
            {
                DateTime DateMin = DateTime.ParseExact("01/01/1753", "MM/dd/yyyy", CultureInfo.InvariantCulture);
                DateTime DateMax = DateTime.ParseExact("12/31/9999", "MM/dd/yyyy", CultureInfo.InvariantCulture);

                if (DateTime.Compare(outDate, DateMin) < 0 || DateTime.Compare(outDate, DateMax) > 0)
                    return DateTime.MinValue;
                else
                    return outDate;
            }

            return DateTime.MinValue;

        }

        /// <summary>
        /// Chuyển đổi chuối "0123456789" sang kiểu Decimal
        /// </summary>
        /// <param name="strDecimalNo">"0123456789"</param>
        /// <returns>Decimal</returns>
        public static Decimal GetDecimalNo(string strDecimalNo)
        {
            Decimal outDec;
            if (Decimal.TryParse(strDecimalNo, out outDec) || outDec > 0)
                return outDec;
            else
                outDec = 0;
            return outDec;
        }

        /// <summary>
        /// Tính tuổi
        /// </summary>
        /// <param name="birthDate">Ngày sinh</param>
        /// <returns>Age</returns>
        public static int GetAge(DateTime birthDate)
        {
            DateTime n = DateTime.Now; // To avoid a race condition around midnight
            int age = n.Year - birthDate.Year;

            if (n.Month < birthDate.Month || (n.Month == birthDate.Month && n.Day < birthDate.Day))
                age--;

            return age;
        }

        /// <summary>
        /// Hàm này để tính số tháng
        /// </summary>
        /// <param name="t1"> Tháng bắt đầu</param>
        /// <param name="t2"> Tháng kết thúc</param>
        /// <returns>Month Number</returns>
        public static int GetMonth(DateTime t1, DateTime t2)
        {
            //Note: t1 <= t2
            int month = (t2.Year - t1.Year) * 12;

            if (t2.Month < t1.Month)
                month -= (t1.Month - t2.Month);
            if (t2.Month > t1.Month)
                month += (t2.Month - t1.Month);
            return month;
        }

        public static int GetDay(DateTime t1, DateTime t2)
        {
            int days;
            //Note: t1 <= t2
            TimeSpan ts;
            ts = t2 - t1;
            days = Convert.ToInt32(ts.TotalDays);
            return days;
        }

        public static bool IsEndOfMonth(DateTime date)
        {
            return new DateTime(date.Year, date.Month, 1).AddMonths(1).AddDays(-1)  == date;
        }

        /// <summary>
        /// Nạp dữ liệu về các độ tuổi cho Combobox
        /// </summary>
        /// <param name="box">ComboBox</param>
        public static void LoadAge(ref ComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("AgeID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("AgeName", typeof(string));
            dt.Columns.Add(dcol);

            for (int j = 18; j <= 70; j++)
            {
                DataRow dr = dt.NewRow();
                dr["AgeID"] = j;
                dr["AgeName"] = j.ToString();
                dt.Rows.InsertAt(dr, j);
            }
            box.ValueMember = "AgeID";
            box.DisplayMember = "AgeName";
            box.DataSource = dt;
        }

        /// <summary>
        /// Lấy ra các quyền dùng theo chức năng
        /// </summary>
        /// <param name="StrRole">Quyền của chức năng</param>
        /// <returns>RoleList</returns>


        /// <summary>
        /// Thêm cột cho DataGridView - Control tự định nghĩa
        /// </summary>
        /// <param name="dgv">dgvUser</param>
        /// <param name="ColumnName">Tên cột</param>
        /// <param name="ColumnHeader">Tiêu đề cột</param>
        /// <param name="Alignment">Căn lề</param>
        /// <param name="Width">Độ rộng của cột</param>
        /// <param name="IsVisible">Trạng thái ẩn</param>
        /// <param name="IsReadOnly">Trạng thái đọc</param>
        public static DataGridViewColumn AddColumn(ref dgvUser dgv, string ColumnName, string ColumnHeader,
            string Alignment, int Width, bool IsVisible, bool IsReadOnly)
        {

            DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
            if (Alignment == "L" || Alignment == "C" || Alignment == "R" || Alignment == "")
            {
                switch (Alignment)
                {
                    case "L":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                        break;
                    case "C":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        break;
                    case "R":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                        break;
                    case "":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                        break;
                }
            }
            else
                col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            col.Name = ColumnName;
            col.HeaderText = ColumnHeader;
            col.Width = Width;
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgv.Columns.Add(col);
            col.Visible = IsVisible;
            col.ReadOnly = IsReadOnly;
            return col;
        }

        /// <summary>
        /// Thêm cột cho DataGridView - Control của hệ thống
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="ColumnName">Tên cột</param>
        /// <param name="ColumnHeader">Tiêu đề cột</param>
        /// <param name="Alignment">Căn lề</param>
        /// <param name="Width">Độ rộng của cột</param>
        /// <param name="IsVisible">Trạng thái ẩn</param>
        /// <param name="IsReadOnly">Trạng thái đọc</param>
        public static DataGridViewColumn AddColumn(ref DataGridView dgv, string ColumnName, string ColumnHeader,
            string Alignment, int Width, bool IsVisible, bool IsReadOnly)
        {

            DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
            if (Alignment == "L" || Alignment == "C" || Alignment == "R" || Alignment == "")
            {
                switch (Alignment)
                {
                    case "L":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                        break;
                    case "C":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        break;
                    case "R":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                        break;
                    case "":
                        col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
                        break;
                }
            }
            else
                col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            col.Name = ColumnName;
            col.HeaderText = ColumnHeader;
            col.DataPropertyName = ColumnName;

            col.Width = Width;
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgv.Columns.Add(col);
            col.Visible = IsVisible;
            col.ReadOnly = IsReadOnly;
            //
            return col;
        }


        /// <summary>
        /// Nạp toàn bộ dữ liệu Tình trạng hôn nhân cho ComboBox
        /// </summary>
        /// <param name="cbo">ComboBox</param>
        public static void LoadMeritalStatus(ref ComboBox cbo)
        {
            try
            {
                MaritalStatusClient wcf = Service.MaritalStatus();
                cbo.DisplayMember = "MaritalStatus_Name";
                cbo.ValueMember = "MaritalStatus_ID";
                cbo.DataSource = wcf.MaritalStatus_GetAll().Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }

        public static void LoadReligion(ref ComboBox cbo)
        {
            try
            {
                ReligionClient wcf = Service.Religion();
                cbo.DisplayMember = "Religion_Name";
                cbo.ValueMember = "Religion_ID";
                cbo.DataSource = wcf.Religion_GetAll().Tables[0];
            }

            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadEthnic(ref ComboBox box)
        {
            try
            {
                EthnicClient wcf = Service.Ethnic();
                DataTable dt = wcf.Ethnic_GetAll().Tables[0];
                box.DataSource = null;
                box.DisplayMember = "Ethnic_Name";
                box.ValueMember = "Ethnic_ID";
                box.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadInsType(ref MultiColumnComboBox box)
        {
            try
            {
                InsTypeClient wcf = Service.InsType();
                DataTable dt = wcf.InsType_GetAll().Tables[0];
                DataRow dr;
                dr = dt.NewRow();
                dr["InsType_Name"] = "";
                dr["InsType_Code"] = "0";
                dt.Rows.InsertAt(dr, 0);
                box.DataSource = null;
                box.DisplayMember = "InsType_Code";
                box.ColumnDisplays = new string[] { "InsType_Code", "InsType_Name" }; ;
                box.ValueMember = "InsType_Code";
                box.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadRecruitment(ref ComboBox cbo, int Year)
        {
            try
            {
                cbo.DataSource = null;
                RecruitmentClient wcf = Service.Recruitment();
                DataTable dt = wcf.Recruitment_GetAll(Year).Tables[0];

                cbo.DisplayMember = "Recruitment_Name";
                cbo.ValueMember = "Recruitment_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadDepartment(ref ComboBox cbo)
        {
            try
            {
                cbo.DataSource = null;
                DepartmentClient wcf = Service.Department();
                DataTable dt = wcf.Department_GetAll().Tables[0];
                int i = 0;
                DataRow[] dtTemp = dt.Select();
                DataRow dr;
                foreach (DataRow drItem in dtTemp)
                {
                    DataTable dtDep = wcf.Department_GetWitchMasterID(Convert.ToInt32(drItem["Department_ID"])).Tables[0];
                    if (dtDep.Rows.Count > 0)
                    {
                        foreach (DataRow drSelectItem in dtDep.Rows)
                        {
                            dr = dt.NewRow();
                            //dr["Department_Name"] = "_____" + drSelectItem["Department_Name"].ToString();
                            dr["Department_Name"] = drItem["Department_Name"].ToString() + " / " + drSelectItem["Department_Name"].ToString();
                            dr["Department_ID"] = drSelectItem["Department_ID"];
                            dt.Rows.InsertAt(dr, i + 1);
                            i++;
                        }

                    }
                    i++;
                }

                cbo.DisplayMember = "Department_Name";
                cbo.ValueMember = "Department_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadDepartment(ref ComboBox cbo, int BranchID)
        {
            try
            {
                cbo.DataSource = null;
                DepartmentClient wcf = Service.Department();
                DataTable dt = wcf.Department_GetWithBranch(BranchID, 1).Tables[0];
                int i = 0;
                DataRow[] dtTemp = dt.Select();
                DataRow dr;
                foreach (DataRow drItem in dtTemp)
                {
                    DataTable dtDep = wcf.Department_GetWitchMasterID(Convert.ToInt32(drItem["Department_ID"])).Tables[0];
                    if (dtDep.Rows.Count > 0)
                    {
                        foreach (DataRow drSelectItem in dtDep.Rows)
                        {
                            dr = dt.NewRow();
                            //dr["Department_Name"] = "_____" + drSelectItem["Department_Name"].ToString();
                            dr["Department_Name"] = drItem["Department_Name"].ToString() + " / " + drSelectItem["Department_Name"].ToString();
                            dr["Department_ID"] = drSelectItem["Department_ID"];
                            dt.Rows.InsertAt(dr, i + 1);
                            i++;
                        }

                    }
                    i++;
                }
                dr = dt.NewRow();
                dr["Department_Name"] = "";
                dr["Department_ID"] = 0;
                dt.Rows.InsertAt(dr, 0);

                cbo.DisplayMember = "Department_Name";
                cbo.ValueMember = "Department_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadDepartment(ref HRM.Utilities.DropDownTreeView tv, int BranchID)
        {
            try
            {
                DepartmentClient wcfDepartment = HRM.Service.Department();
                TreeNode node, node2;
                DataTable dt2;

                tv.Nodes.Clear();
                node = tv.Nodes.Add("Root", "", 0, 0);
                node.Tag = 0;
                node.ExpandAll();


                dt2 = wcfDepartment.Department_GetWithBranch(BranchID, 1).Tables[0];
                foreach (DataRow dr2 in dt2.Rows)
                {
                    node2 = node.Nodes.Add(dr2["Department_ID"].ToString(), dr2["Department_Name"].ToString(), 2, 3);
                    node2.Tag = Convert.ToInt32(dr2["Department_ID"]);
                    node2.ExpandAll();
                    AddChildNode(node2);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadDepartment(ref ToolStripComboBox cbo, int BranchID)
        {
            try
            {
                cbo.ComboBox.DataSource = null;
                DepartmentClient wcf = Service.Department();
                DataTable dt = wcf.Department_GetWithBranch(BranchID, 1).Tables[0];
                DataRow[] dtTemp = dt.Select();
                int i = 0;
                DataRow dr;
                foreach (DataRow drItem in dtTemp)
                {
                    DataTable dtDep = wcf.Department_GetWitchMasterID(Convert.ToInt32(drItem["Department_ID"])).Tables[0];
                    foreach (DataRow drSelectItem in dtDep.Rows)
                    {
                        dr = dt.NewRow();
                        dr["Department_Name"] = "     " + drSelectItem["Department_Name"].ToString();
                        dr["Department_ID"] = drSelectItem["Department_ID"];
                        dt.Rows.InsertAt(dr, i + 1);
                        i++;
                    }
                    i++;
                }
                dr = dt.NewRow();
                dr["Department_Name"] = "";
                dr["Department_ID"] = 0;
                dt.Rows.InsertAt(dr, 0);
                cbo.ComboBox.DisplayMember = "Department_Name";
                cbo.ComboBox.ValueMember = "Department_ID";
                cbo.ComboBox.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }

        public static void LoadDepartment(ref ToolStripComboBox cbo)
        {
            try
            {
                cbo.ComboBox.DataSource = null;
                DepartmentClient wcf = Service.Department();
                DataTable dt = wcf.Department_GetAll().Tables[0];
                DataRow[] dtTemp = dt.Select();
                int i = 0;
                DataRow dr;
                foreach (DataRow drItem in dtTemp)
                {
                    DataTable dtDep = wcf.Department_GetWitchMasterID(Convert.ToInt32(drItem["Department_ID"])).Tables[0];
                    foreach (DataRow drSelectItem in dtDep.Rows)
                    {
                        dr = dt.NewRow();
                        dr["Department_Name"] = "   " + drSelectItem["Department_Name"].ToString();
                        dr["Department_ID"] = drSelectItem["Department_ID"];
                        dt.Rows.InsertAt(dr, i + 1);
                        i++;
                    }
                    i++;
                }
                dr = dt.NewRow();
                dr["Department_Name"] = "";
                dr["Department_ID"] = 0;
                dt.Rows.InsertAt(dr, 0);
                cbo.ComboBox.DisplayMember = "Department_Name";
                cbo.ComboBox.ValueMember = "Department_ID";
                cbo.ComboBox.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }

        public static void LoadGrade(ref ComboBox cbo, int _BranchID)
        {
            try
            {
                cbo.DataSource = null;
                GradeClient wcfGrade = Service.Grade();
                DataTable dt = new DataTable();
                if (_BranchID > 0)
                    dt = wcfGrade.Grade_GetByBranch(_BranchID).Tables[0];
                else
                    dt = wcfGrade.Grade_GetAll().Tables[0];
                //DataRow dr = dt.NewRow();
                //dr["Grade_Name"] = "";
                //dr["Grade_ID"] = 0;
                //dt.Rows.InsertAt(dr, 0);
                cbo.DisplayMember = "Grade_Name";
                cbo.ValueMember = "Grade_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadLevel(ref ComboBox cbo, int _GradeID)
        {
            try
            {
                cbo.DataSource = null;
                LevelClient wcfLevel = Service.Level();
                DataTable dt = new DataTable();
                dt = wcfLevel.Level_GetByGrade(_GradeID).Tables[0];
                cbo.DisplayMember = "Level_Name";
                cbo.ValueMember = "Level_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadPosition(ref ComboBox cbo)
        {
            try
            {
                cbo.DataSource = null;
                PositionClient wcf = Service.Position();
                DataTable dt = wcf.Position_GetAll().Tables[0];
                cbo.DisplayMember = "Position_Name";
                cbo.ValueMember = "Position_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadPositionByBranchCode(ref ComboBox cbo, string _BranchCode)
        {
            try
            {
                cbo.DataSource = null;
                PositionClient wcf = Service.Position();
                DataTable dt = wcf.Position_GetByBranchCode(_BranchCode).Tables[0];
                //
                DataRow dr = dt.NewRow();
                dr["Position_Name"] = "";
                dr["Position_ID"] = 0;
                dt.Rows.InsertAt(dr, 0);
                //
                cbo.DisplayMember = "Position_Name";
                cbo.ValueMember = "Position_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadBranch(ref ComboBox box)
        {
            try
            {
                box.DataSource = null;
                BranchClient wcf = Service.Branch();
                DataTable dt;
                if (!Program.isAdmin && !Program.isViewAll)
                {
                    dt = wcf.Branch_GetOne(Program.intBranchID).Tables[0];
                }
                else
                {
                    dt = wcf.Branch_GetAll().Tables[0];
                    DataRow dr = dt.NewRow();
                    dr["Branch_Name"] = "---Chọn Đơn vị---";
                    dr["Branch_ID"] = 0;
                    dt.Rows.InsertAt(dr, 0);
                }
                box.DisplayMember = "Branch_Name";
                box.ValueMember = "Branch_ID";
                box.DataSource = dt;

            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadBranchByRegionCode(ref ComboBox box, string _RegionCode)
        {
            try
            {
                box.DataSource = null;
                BranchClient wcf = Service.Branch();
                DataTable dt;
                //if (!Program.isAdmin && !Program.isViewAll)
                //{
                //    dt = wcf.Branch_GetOne(Program.intBranchID).Tables[0];
                //}
                //else
                {
                    dt = wcf.Branch_GetByRegionCode(_RegionCode).Tables[0];
                    DataRow dr = dt.NewRow();
                    dr["Branch_Name"] = "---Chọn Đơn vị---";
                    dr["Branch_ID"] = 0;
                    dt.Rows.InsertAt(dr, 0);
                }
                box.DisplayMember = "Branch_Name";
                box.ValueMember = "Branch_ID";
                box.DataSource = dt;

            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadBranchByRegionCode(ref ToolStripComboBox box, string _RegionCode)
        {
            try
            {
                box.ComboBox.DataSource = null;
                BranchClient wcf = Service.Branch();
                DataTable dt;
                //if (!Program.isAdmin && !Program.isViewAll)
                //{
                //    dt = wcf.Branch_GetOne(Program.intBranchID).Tables[0];
                //}
                //else
                {
                    dt = wcf.Branch_GetByRegionCode(_RegionCode).Tables[0];
                    DataRow dr = dt.NewRow();
                    dr["Branch_Name"] = "---Chọn Đơn vị---";
                    dr["Branch_ID"] = 0;
                    dt.Rows.InsertAt(dr, 0);
                }
                box.ComboBox.DisplayMember = "Branch_Name";
                box.ComboBox.ValueMember = "Branch_ID";
                box.ComboBox.DataSource = dt;

            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadRegion(ref ComboBox box)
        {
            try
            {
                box.DataSource = null;
                RegionClient wcf = Service.Region();
                DataTable dt;
                dt = wcf.Region_GetAll().Tables[0];
                DataRow dr = dt.NewRow();
                dr["Region_Name"] = "---Chọn Tỉnh/TP---";
                dr["Region_Code"] = 0;
                dt.Rows.InsertAt(dr, 0);
                box.DisplayMember = "Region_Name";
                box.ValueMember = "Region_Code";
                box.DataSource = dt;
                box.SelectedValue = Program.strRegionCode;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadRegion(ref ToolStripComboBox box)
        {
            try
            {
                box.ComboBox.DataSource = null;
                RegionClient wcf = Service.Region();
                DataTable dt;
                dt = wcf.Region_GetAll().Tables[0];
                DataRow dr = dt.NewRow();
                dr["Region_Name"] = "---Chọn Tỉnh/TP---";
                dr["Region_Code"] = 0;
                dt.Rows.InsertAt(dr, 0);
                box.ComboBox.DisplayMember = "Region_Name";
                box.ComboBox.ValueMember = "Region_Code";
                box.ComboBox.DataSource = dt;
                box.ComboBox.SelectedValue = Program.strRegionCode;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }

        public static void LoadBranch(ref ToolStripComboBox box)
        {
            try
            {
                box.ComboBox.DataSource = null;
                BranchClient wcf = Service.Branch();
                DataTable dt;
                if (!Program.isAdmin && !Program.isViewAll)
                {
                    dt = wcf.Branch_GetOne(Program.intBranchID).Tables[0];
                }
                else
                    dt = wcf.Branch_GetAll().Tables[0];


                DataRow dr = dt.NewRow();
                dr["Branch_Name"] = "";
                dr["Branch_ID"] = 0;
                dt.Rows.InsertAt(dr, 0);

                box.ComboBox.DisplayMember = "Branch_Name";
                box.ComboBox.ValueMember = "Branch_ID";
                box.ComboBox.DataSource = dt;

            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadEmployeeByDepartment(ref ToolStripComboBox box, int DeptID)
        {
            try
            {
                box.ComboBox.DataSource = null;
                EmployeeClient service = Service.Employee();
                DataTable dt = service.Employee_GetByDepartment(DeptID).Tables[0];

                box.ComboBox.DisplayMember = "Employee_FullName";
                box.ComboBox.ValueMember = "Employee_ID";
                box.ComboBox.DataSource = dt;

            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadContractType(ref ComboBox cbo)
        {
            try
            {
                ContractTypeClient wcf = Service.ContractType();
                DataTable dt = wcf.ContractType_GetAll().Tables[0];
                cbo.DisplayMember = "ContractType_Name";
                cbo.ValueMember = "ContractType_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadContractTypeByBranchCode(ref ComboBox cbo, string _BranchCode)
        {
            try
            {
                ContractTypeClient wcf = Service.ContractType();
                DataTable dt = wcf.ContractType_GetByBranchCode(_BranchCode).Tables[0];
                //
                DataRow dr = dt.NewRow();
                dr["ContractType_Name"] = "";
                dr["ContractType_ID"] = 0;
                dt.Rows.InsertAt(dr, 0);
                //
                cbo.DisplayMember = "ContractType_Name";
                cbo.ValueMember = "ContractType_ID";
                cbo.DataSource = dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }

        public static void LoadMisConduct(ref ComboBox cbo)
        {
            try
            {
                MisConductClient wcf = Service.MisConduct();
                DataTable dt = wcf.MisConduct_GetAll().Tables[0];
                //DataRow dr = dt.NewRow();
                //dr["MisConduct_Name"] = "";
                //dr["MisConduct_ID"] = 0;
                //dt.Rows.InsertAt(dr, 0);
                cbo.DataSource = dt;
                cbo.DisplayMember = "MisConduct_Name";
                cbo.ValueMember = "MisConduct_ID";
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }

        public static void LoadYears(ref ComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("YearID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("YearName", typeof(string));
            dt.Columns.Add(dcol);

            for (int j = DateTime.Now.Year - 5; j <= DateTime.Now.Year; j++)
            {
                DataRow dr = dt.NewRow();
                dr["YearID"] = j;
                dr["YearName"] = j.ToString();
                dt.Rows.InsertAt(dr, j);
            }
            box.ValueMember = "YearID";
            box.DisplayMember = "YearName";
            box.DataSource = dt;
            box.SelectedValue = DateTime.Today.Year;
        }
        public static void LoadDays(ref ComboBox box)
        {

            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("DayID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("DayName", typeof(string));
            dt.Columns.Add(dcol);

            for (int i = 1; i <= 31; i++)
            {
                DataRow dr = dt.NewRow();
                dr["DayID"] = i;
                if (i < 10)
                    dr["DayName"] = "0" + i.ToString();
                else
                    dr["DayName"] = i.ToString();
                dt.Rows.InsertAt(dr, i);
            }
            box.ValueMember = "DayID";
            box.DisplayMember = "DayName";
            box.DataSource = dt;
        }
        public static void LoadMonths(ref ComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("MonthID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("MonthName", typeof(string));
            dt.Columns.Add(dcol);
            for (int i = 12; i >= 1; i--)
            {
                DataRow dr = dt.NewRow();
                dr["MonthID"] = i;
                if (i < 10)
                    dr["MonthName"] = "0" + i.ToString();
                else
                    dr["MonthName"] = i.ToString();
                dt.Rows.InsertAt(dr, 0);
            }
            box.ValueMember = "MonthID";
            box.DisplayMember = "MonthName";
            box.DataSource = dt;
        }
        public static void LoadMonths(ref ToolStripComboBox box)
        {
            box.ComboBox.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("MonthID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("MonthName", typeof(string));
            dt.Columns.Add(dcol);

            for (int i = 1; i <= 12; i++)
            {
                DataRow dr = dt.NewRow();
                dr["MonthID"] = i;
                dr["MonthName"] = i.ToString("00");
                dt.Rows.InsertAt(dr, i);
            }

            box.ComboBox.ValueMember = "MonthID";
            box.ComboBox.DisplayMember = "MonthName";
            box.ComboBox.DataSource = dt;
            box.ComboBox.SelectedValue = DateTime.Today.Month;
        }

        public static void LoadMonths(ref ToolStripComboBox box, bool appendAll)
        {
            if (!appendAll)
            {
                LoadMonths(ref box);
            }
            else
            {
                box.ComboBox.DataSource = null;
                DataTable dt = new DataTable();
                DataColumn dcol = null;
                dcol = new DataColumn("MonthID", typeof(int));
                dt.Columns.Add(dcol);
                dcol = new DataColumn("MonthName", typeof(string));
                dt.Columns.Add(dcol);

                for (int i = 0; i <= 12; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["MonthID"] = i;
                    dr["MonthName"] = (i == 0 ? "Tất cả" : i.ToString("00"));
                    dt.Rows.InsertAt(dr, i);
                }

                box.ComboBox.ValueMember = "MonthID";
                box.ComboBox.DisplayMember = "MonthName";
                box.ComboBox.DataSource = dt;
                box.ComboBox.SelectedValue = DateTime.Today.Month;
            }
        }

        public static void LoadYears(ref ToolStripComboBox box)
        {
            box.Width = 55;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("YearID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("YearName", typeof(string));
            dt.Columns.Add(dcol);
            int currenYear = DateTime.Today.Year;

            for (int i = currenYear - 5; i <= currenYear; i++)
            {
                DataRow dr = dt.NewRow();
                dr["YearID"] = i;
                dr["YearName"] = i.ToString("0000");
                dt.Rows.InsertAt(dr, i);
            }
            box.ComboBox.ValueMember = "YearID";
            box.ComboBox.DisplayMember = "YearName";
            box.ComboBox.DataSource = null;
            box.ComboBox.DataSource = dt;
            box.ComboBox.SelectedValue = currenYear;
        }
        public static void NapTrangThaiHoSo(ref ComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("ID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);
            DataRow dr = dt.NewRow();

            dr["ID"] = 1;
            dr["Name"] = "Nhập hồ sơ";
            dt.Rows.InsertAt(dr, 0);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Name"] = "Đã phỏng vấn lần 1";
            dt.Rows.InsertAt(dr, 1);

            dr = dt.NewRow();
            dr["ID"] = 3;
            dr["Name"] = "Trượt phỏng vấn lần 1";
            dt.Rows.InsertAt(dr, 2);

            dr = dt.NewRow();
            dr["ID"] = 4;
            dr["name"] = "Đã phỏng vấn lần 2";
            dt.Rows.InsertAt(dr, 3);

            dr = dt.NewRow();
            dr["ID"] = 5;
            dr["Name"] = "Trượt phỏng vấn lần 2";
            dt.Rows.InsertAt(dr, 4);

            dr = dt.NewRow();
            dr["ID"] = 6;
            dr["Name"] = "Đã phỏng vấn lần 3";
            dt.Rows.InsertAt(dr, 5);

            dr = dt.NewRow();
            dr["ID"] = 7;
            dr["Name"] = "Trượt phỏng vấn lần 3";
            dt.Rows.InsertAt(dr, 6);

            dr = dt.NewRow();
            dr["ID"] = 8;
            dr["Name"] = "Trúng tuyển";
            dt.Rows.InsertAt(dr, 7);

            box.ValueMember = "ID";
            box.DisplayMember = "Name";
            box.DataSource = dt;
        }
        public static void NapTrangThaiHoSo(ref ToolStripComboBox box)
        {
            box.ComboBox.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("ID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);
            DataRow dr = dt.NewRow();

            dr["ID"] = 0;
            dr["Name"] = "...Tất cả...";
            dt.Rows.InsertAt(dr, 0);

            dr = dt.NewRow();
            dr["ID"] = 1;
            dr["Name"] = "Nhập hồ sơ";
            dt.Rows.InsertAt(dr, 1);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Name"] = "Đã phỏng vấn lần 1";
            dt.Rows.InsertAt(dr, 2);

            dr = dt.NewRow();
            dr["ID"] = 3;
            dr["Name"] = "Trượt phỏng vấn lần 1";
            dt.Rows.InsertAt(dr, 3);

            dr = dt.NewRow();
            dr["ID"] = 4;
            dr["name"] = "Đã phỏng vấn lần 2";
            dt.Rows.InsertAt(dr, 4);

            dr = dt.NewRow();
            dr["ID"] = 5;
            dr["Name"] = "Trượt phỏng vấn lần 2";
            dt.Rows.InsertAt(dr, 5);

            dr = dt.NewRow();
            dr["ID"] = 6;
            dr["Name"] = "Đã phỏng vấn lần 3";
            dt.Rows.InsertAt(dr, 6);

            dr = dt.NewRow();
            dr["ID"] = 7;
            dr["Name"] = "Trượt phỏng vấn lần 3";
            dt.Rows.InsertAt(dr, 7);

            dr = dt.NewRow();
            dr["ID"] = 8;
            dr["Name"] = "Trúng tuyển";
            dt.Rows.InsertAt(dr, 8);

            box.ComboBox.ValueMember = "ID";
            box.ComboBox.DisplayMember = "Name";
            box.ComboBox.DataSource = dt;
        }
        public static void LoadPointByCriteria(string criteria, ref ComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);

            DataRow dr = dt.NewRow();
            switch (criteria)
            {
                case "Appearance":
                    dt.Rows.Clear();
                    dr = dt.NewRow();
                    dr["Name"] = "Sloppy";
                    dt.Rows.InsertAt(dr, 0);

                    dr = dt.NewRow();
                    dr["Name"] = "Not Orfanized";
                    dt.Rows.InsertAt(dr, 1);

                    dr = dt.NewRow();
                    dr["Name"] = "Function";
                    dt.Rows.InsertAt(dr, 2);

                    dr = dt.NewRow();
                    dr["Name"] = "Well Groomed";
                    dt.Rows.InsertAt(dr, 3);

                    dr = dt.NewRow();
                    dr["Name"] = "Warm & Charming";
                    dt.Rows.InsertAt(dr, 4);
                    break;
                case "Personality":
                    dt.Rows.Clear();
                    dr = dt.NewRow();
                    dr["Name"] = "Unpleasant";
                    dt.Rows.InsertAt(dr, 5);

                    dr = dt.NewRow();
                    dr["Name"] = "Needs effort";
                    dt.Rows.InsertAt(dr, 6);

                    dr = dt.NewRow();
                    dr["Name"] = "Kieable";
                    dt.Rows.InsertAt(dr, 7);

                    dr = dt.NewRow();
                    dr["Name"] = "Pleasing";
                    dt.Rows.InsertAt(dr, 8);

                    dr = dt.NewRow();
                    dr["Name"] = "Professional looking";
                    dt.Rows.InsertAt(dr, 9); break;
                case "Motivation":
                    dt.Rows.Clear();
                    dr = dt.NewRow();
                    dr["Name"] = "No drive";
                    dt.Rows.InsertAt(dr, 10);

                    dr = dt.NewRow();
                    dr["Name"] = "Doubtfull Interest";
                    dt.Rows.InsertAt(dr, 11);

                    dr = dt.NewRow();
                    dr["Name"] = "Sincere Desire";
                    dt.Rows.InsertAt(dr, 12);

                    dr = dt.NewRow();
                    dr["Name"] = "Strong Interrest";
                    dt.Rows.InsertAt(dr, 13);

                    dr = dt.NewRow();
                    dr["Name"] = "Highly motivated";
                    dt.Rows.InsertAt(dr, 14);
                    break;
                case "Communication":
                    dt.Rows.Clear();
                    dr = dt.NewRow();
                    dr["Name"] = "Confused Poor";
                    dt.Rows.InsertAt(dr, 15);

                    dr = dt.NewRow();
                    dr["Name"] = "Hazy";
                    dt.Rows.InsertAt(dr, 16);

                    dr = dt.NewRow();
                    dr["Name"] = "Answer to Questions";
                    dt.Rows.InsertAt(dr, 17);

                    dr = dt.NewRow();
                    dr["Name"] = "Speaks wwith confidence";
                    dt.Rows.InsertAt(dr, 18);

                    dr = dt.NewRow();
                    dr["Name"] = "Concise/Clear thoughts";
                    dt.Rows.InsertAt(dr, 19);
                    break;
                case "Qualification":
                    dt.Rows.Clear();
                    dr = dt.NewRow();
                    dr["Name"] = "Not Adequate";
                    dt.Rows.InsertAt(dr, 0);

                    dr = dt.NewRow();
                    dr["Name"] = "Marginal";
                    dt.Rows.InsertAt(dr, 1);

                    dr = dt.NewRow();
                    dr["Name"] = "Suitable";
                    dt.Rows.InsertAt(dr, 2);

                    dr = dt.NewRow();
                    dr["Name"] = "Well qualified";
                    dt.Rows.InsertAt(dr, 3);

                    dr = dt.NewRow();
                    dr["Name"] = "Ideal";
                    dt.Rows.InsertAt(dr, 4);
                    break;
                case "Job Knowledge":
                    dt.Rows.Clear();

                    dr = dt.NewRow();
                    dr["Name"] = "None";
                    dt.Rows.InsertAt(dr, 0);

                    dr = dt.NewRow();
                    dr["Name"] = "Needs Traning";
                    dt.Rows.InsertAt(dr, 1);

                    dr = dt.NewRow();
                    dr["Name"] = "Basic Knowledge";
                    dt.Rows.InsertAt(dr, 2);

                    dr = dt.NewRow();
                    dr["Name"] = "Good Knowledge";
                    dt.Rows.InsertAt(dr, 3);

                    dr = dt.NewRow();
                    dr["Name"] = "Excellent Knowledge";
                    dt.Rows.InsertAt(dr, 4);
                    break;
            }


            box.ValueMember = "Name";
            box.DisplayMember = "Name";
            box.DataSource = dt;
        }
        public static void SetPointByCriteria(string criteria, ref RadioButton rdn1, ref RadioButton rdn2, ref RadioButton rdn3, ref RadioButton rdn4, ref RadioButton rdn5)
        {
            switch (criteria)
            {
                case "Appearance":
                    rdn1.Text = "Sloppy";
                    rdn2.Text = "Not Orfanized";
                    rdn3.Text = "Function";
                    rdn4.Text = "Well Groomed";
                    rdn5.Text = "Professional looking";
                    break;
                case "Personality":
                    rdn1.Text = "Unpleasant";
                    rdn2.Text = "Needs effort";
                    rdn3.Text = "Kieable";
                    rdn4.Text = "Pleasing";
                    rdn5.Text = "Warm & Charming";
                    break;
                case "Motivation":
                    rdn1.Text = "No drive";
                    rdn2.Text = "Doubtfull Interest";
                    rdn3.Text = "Sincere Desire";
                    rdn4.Text = "Strong Interrest";
                    rdn5.Text = "Highly motivated";
                    break;
                case "Communication":
                    rdn1.Text = "Confused Poor";
                    rdn2.Text = "Hazy";
                    rdn3.Text = "Answer to Questions";
                    rdn4.Text = "Speaks wwith confidence";
                    rdn5.Text = "Concise/Clear thoughts";
                    break;
                case "Qualification":
                    rdn1.Text = "Not Adequate";
                    rdn2.Text = "Marginal";
                    rdn3.Text = "Suitable";
                    rdn4.Text = "Well qualified";
                    rdn5.Text = "Ideal";
                    break;
                case "Job Knowledge":
                    rdn1.Text = "None";
                    rdn2.Text = "Needs Traning";
                    rdn3.Text = "Basic Knowledge";
                    rdn4.Text = "Good Knowledge";
                    rdn5.Text = "Excellent Knowledge";
                    break;
            }
        }
        public static void LoadAbility(ref CustomUIControls.MultiColumnComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("Code", typeof(string));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);

            DataRow dr = dt.NewRow();
            dr["Code"] = "";
            dr["Name"] = "";
            dt.Rows.InsertAt(dr, 0);

            dr = dt.NewRow();
            dr["Code"] = "E";
            dr["Name"] = UpdateUI.GetString("xuatsac");
            dt.Rows.InsertAt(dr, 1);

            dr = dt.NewRow();
            dr["Code"] = "VG";
            dr["Name"] = UpdateUI.GetString("rattot");
            dt.Rows.InsertAt(dr, 2);

            dr = dt.NewRow();
            dr["Code"] = "G";
            dr["Name"] = UpdateUI.GetString("tot");
            dt.Rows.InsertAt(dr, 3);

            dr = dt.NewRow();
            dr["Code"] = "AV";
            dr["Name"] = UpdateUI.GetString("trungbinh");
            dt.Rows.InsertAt(dr, 4);

            dr = dt.NewRow();
            dr["Code"] = "P";
            dr["Name"] = UpdateUI.GetString("kem");
            dt.Rows.InsertAt(dr, 5);

            box.DisplayMember = "Code";
            box.ColumnDisplays = new string[] { "Code", "Name" };
            box.ValueMember = "Code";
            box.DataSource = dt;
        }

        public static void LoadRecruimentStatus(ref ToolStripComboBox box)
        {
            box.ComboBox.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("ID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);

            DataRow dr = dt.NewRow();
            dr["ID"] = 0;
            dr["Name"] = "Chưa tham gia tuyển dụng";
            dt.Rows.InsertAt(dr, 0);

            dr = dt.NewRow();
            dr["ID"] = 1;
            dr["Name"] = "Đã tham gia tuyển dụng";
            dt.Rows.InsertAt(dr, 1);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Name"] = "Trượt";
            dt.Rows.InsertAt(dr, 2);

            box.ComboBox.ValueMember = "ID";
            box.ComboBox.DisplayMember = "Name";
            box.ComboBox.DataSource = dt;
        }
        public static void LoadRecruimentStatus1(ref ToolStripComboBox box)
        {
            box.ComboBox.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("ID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);

            DataRow dr = dt.NewRow();
            dr["ID"] = 0;
            dr["Name"] = "Chưa tham gia tuyển dụng";
            dt.Rows.InsertAt(dr, 0);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Name"] = "Trượt";
            dt.Rows.InsertAt(dr, 2);

            box.ComboBox.ValueMember = "ID";
            box.ComboBox.DisplayMember = "Name";
            box.ComboBox.DataSource = dt;
        }
        public static void NapTrangThaiDuyet(ref ComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("ID", typeof(int));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);

            DataRow dr = dt.NewRow();
            dr["ID"] = 0;
            dr["Name"] = "---Chọn kết luận---";
            dt.Rows.InsertAt(dr, 0);

            dr = dt.NewRow();
            dr["ID"] = 1;
            dr["Name"] = "Đạt yêu cầu";
            dt.Rows.InsertAt(dr, 1);

            dr = dt.NewRow();
            dr["ID"] = 2;
            dr["Name"] = "Không đạt yêu cầu";
            dt.Rows.InsertAt(dr, 2);

            dr = dt.NewRow();
            dr["ID"] = 3;
            dr["Name"] = "Cần phỏng vấn lần 2";
            dt.Rows.InsertAt(dr, 3);

            dr = dt.NewRow();
            dr["ID"] = 4;
            dr["Name"] = "Cần gặp lãnh đạo cấp cao hơn";
            dt.Rows.InsertAt(dr, 4);

            box.ValueMember = "ID";
            box.DisplayMember = "Name";
            box.DataSource = dt;
        }
        public static void NapLoaiKhenThuong(ref ComboBox box)
        {
            box.DataSource = null;
            DataTable dt = new DataTable();
            DataColumn dcol = null;
            dcol = new DataColumn("ID", typeof(string));
            dt.Columns.Add(dcol);
            dcol = new DataColumn("Name", typeof(string));
            dt.Columns.Add(dcol);

            DataRow dr = dt.NewRow();
            dr["ID"] = "KT";
            dr["Name"] = "Khen thưởng";
            dt.Rows.InsertAt(dr, 0);

            dr = dt.NewRow();
            dr["ID"] = "KL";
            dr["Name"] = "Kỷ luật";
            dt.Rows.InsertAt(dr, 1);

            box.ValueMember = "ID";
            box.DisplayMember = "Name";
            box.DataSource = dt;
        }
        public static void LoadGrantParent(ref ComboBox box)
        {
            try
            {
                MainServiceClient wcf = Service.MainService();
                string sql = "Select Grant_ID, Grant_Name from HRM_Grant Where Grant_ParentID = 0";
                box.DisplayMember = "Grant_Name";
                box.ValueMember = "Grant_ID";
                box.DataSource = wcf.Query(sql).Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadGrantParentFormale(ref ComboBox box)
        {
            try
            {
                MainServiceClient wcf = Service.MainService();
                string sql = "Select Grant_ID, Grant_Name from HRM_Grant Where Grant_ParentID = 0 and Grant_ForFemale is null";
                box.DisplayMember = "Grant_Name";
                box.ValueMember = "Grant_ID";
                box.DataSource = wcf.Query(sql).Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }
        }
        public static void LoadGrantSub(string Grant_ID, ref ComboBox box)
        {
            try
            {
                box.DataSource = null;
                MainServiceClient _wcf = Service.MainService();
                string _sql = "Select Grant_ID, Grant_Name from HRM_Grant Where Grant_ParentID = " + Grant_ID;
                box.DisplayMember = "Grant_Name";
                box.ValueMember = "Grant_ID";
                box.DataSource = _wcf.Query(_sql).Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(UpdateUI.GetString("loi") + ":\n" + ex.Message + "\n" + UpdateUI.GetString("ghiloi"));
            }

        }
        #region MessageBox

        public static void MesNotRightNew()
        {
            MessageBox.Show(UpdateUI.GetString("khongcoquyenthemmoi"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        public static void MesNotRightEdit()
        {
            MessageBox.Show(UpdateUI.GetString("khongcoquyencapnhat"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        public static void MesNotRightView()
        {
            MessageBox.Show(UpdateUI.GetString("khongcoquyenxem"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        public static void MesNotRightPrint()
        {
            MessageBox.Show(UpdateUI.GetString("khongcoquyenin"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        public static void MesNotRightDelete()
        {
            MessageBox.Show(UpdateUI.GetString("khongcoquyenxoa"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
        public static void MesNotUpdate()
        {
            MessageBox.Show(UpdateUI.GetString("capnhatkhongthanhcong"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        public static void MesNotCreate()
        {
            MessageBox.Show(UpdateUI.GetString("themmoikhongthanhcong"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        public static void MesNotDelete()
        {
            MessageBox.Show(UpdateUI.GetString("xoakhongthanhcong"), UIParamater.TitleProject, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        public static void MesUpdateSuccesful()
        {
            Program.taskbarNotifier.Show(UpdateUI.GetString("thongbao"), UpdateUI.GetString("capnhatthanhcong"), UIParamater.TIME_MOVE_UP, UIParamater.TIME_SHOW, UIParamater.TIME_MOVE_DOWN);
        }
        public static void MesCreateSuccesful()
        {
            Program.taskbarNotifier.Show(UpdateUI.GetString("thongbao"), UpdateUI.GetString("themmoithanhcong"), UIParamater.TIME_MOVE_UP, UIParamater.TIME_SHOW, UIParamater.TIME_MOVE_DOWN);
        }
        public static void MesDeleteSuccesful()
        {
            Program.taskbarNotifier.Show(UpdateUI.GetString("thongbao"), UpdateUI.GetString("xoathanhcong"), UIParamater.TIME_MOVE_UP, UIParamater.TIME_SHOW, UIParamater.TIME_MOVE_DOWN);
        }

        #endregion
    }

    public class ReadExcelFile
    {
        public static String[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {
                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                objConn = new OleDbConnection(connString);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                return excelSheets;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        public static DataSet GetExcelData(string excelFile, string sheetName)
        {
            DataSet ds = new DataSet();
            String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
            string strSql = "Select * From [" + sheetName + "]";
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, connString);
            try
            {
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                strSql = ":\n" + ex.Message + "\n";
                return null;
            }

        }
        public static DataSet ReadExcelList(string fileName)
        {
            DataSet myDataSet = new DataSet();
            string strSql = "SELECT * ";
            //string strSql = "SELECT CHU_XE,DIA_CHI,HIEU_LUC_TU,DEN_NGAY,BIEN_SO,SO_GCNBH,SO_GCNBHVC,";
            //strSql = strSql + " HIEU_XE,LOAI_XE,SO_KHUNG,SO_MAY,SO_NAM_SD,GIA_TRI_XE,";
            //strSql = strSql + " TNDS3_NGUOI,TNDS3_TAI_SAN,PHI_TNDS3,THUE_TNDS3,SO_CHO,TRONG_TAI,BH_NGUOI_NGOI,PHI_BH_NGUOI_NGOI,";
            //strSql = strSql + " BH_VAT_CHAT,SO_TIEN_BH,PHI_BHVC,THUE_BHVC,MIEN_THUONG,TI_LE_PHI,BSUNG,BS_KHAC";
            strSql = strSql + " FROM [Sheet1$] WHERE HOTEN <> ''";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + "; Extended Properties=Excel 8.0;";
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strSql, strConn);

            try
            {
                myCommand.Fill(myDataSet);
            }
            finally
            {
                strSql = "";
            }
            return myDataSet;
        }

        public static void exportToExcel(string strTitle, DataSet source, string fileName)
        {

            System.IO.StreamWriter excelDoc;

            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n" +
                  "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n " +
                  "xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n " +
                  "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n " +
                  "<Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Borders/>\r\n " +
                  "<Font/>\r\n " +
                  "<Interior/>\r\n " +
                  "<NumberFormat/>\r\n " +
                  "<Protection/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n" +
                  "<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n " +
                  "<Interior ss:Color=\"#CCFFCC\" ss:Pattern=\"Solid\"/>\r\n" +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"StringLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Decimal\">\r\n " +
                  "<NumberFormat ss:Format=\"#,##0.0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n " +
                  "<NumberFormat ss:Format=\"0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"DateLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"dd/MM/yyyy;@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Title\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Font x:Family=\"Swiss\" ss:Size=\"14\" ss:Bold=\"1\"/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Normal\">\r\n " +
                  "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "</Style>\r\n " +
                  "</Styles>\r\n ";

            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;

            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">\r\n ");
            excelDoc.Write("<Table x:FullColumns=\"1\" x:FullRows=\"1\" >");

            for (int x = 0; x < source.Tables[0].Columns.Count; x++)
            {
                excelDoc.Write("<Column ss:Width=\"100\" ss:AutoFitWidth=\"1\" />\r\n ");
            }

            excelDoc.Write("<Row ss:Height=\"18\">\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"5\" ss:StyleID=\"Title\"><Data ss:Type=\"String\">" + strTitle + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");

            excelDoc.Write("<Row>\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"5\" ss:StyleID=\"Normal\"><Data ss:Type=\"String\"> Tổng số dòng: " + source.Tables[0].Rows.Count.ToString() + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");

            excelDoc.Write("<Row>\r\n ");
            for (int x = 0; x < source.Tables[0].Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Tables[0].Columns[x].Caption);
                excelDoc.Write("</Data></Cell>\r\n ");
            }
            excelDoc.Write("</Row>\r\n ");
            foreach (DataRow x in source.Tables[0].Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output

                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "

                for (int y = 0; y < source.Tables[0].Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  

                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000

                            //The Following Code puts the date stored in XMLDate 

                            //to the format above

                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date

                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }
        public static void exportToExcel(string strTitle, DataTable source, string fileName)
        {

            System.IO.StreamWriter excelDoc;
            //if (File.Exists(fileName))
            //    fileName = fileName.Replace(".xls", "02.xls");
            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n" +
                  "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n " +
                  "xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n " +
                  "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n " +
                  "<Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Borders/>\r\n " +
                  "<Font/>\r\n " +
                  "<Interior/>\r\n " +
                  "<NumberFormat/>\r\n " +
                  "<Protection/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n" +
                  "<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n " +
                  "<Interior ss:Color=\"#CCFFCC\" ss:Pattern=\"Solid\"/>\r\n" +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"StringLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Decimal\">\r\n " +
                  "<NumberFormat ss:Format=\"#,##0.0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n " +
                  "<NumberFormat ss:Format=\"0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"DateLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"dd/MM/yyyy;@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Title\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Font x:Family=\"Swiss\" ss:Size=\"14\" ss:Bold=\"1\"/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Normal\">\r\n " +
                  "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "</Style>\r\n " +
                  "</Styles>\r\n ";

            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;

            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">\r\n ");
            excelDoc.Write("<Table x:FullColumns=\"1\" x:FullRows=\"1\" >");

            for (int x = 0; x < source.Columns.Count; x++)
            {
                excelDoc.Write("<Column ss:Width=\"100\" ss:AutoFitWidth=\"1\" />\r\n ");
            }

            excelDoc.Write("<Row ss:Height=\"18\">\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"5\" ss:StyleID=\"Title\"><Data ss:Type=\"String\">" + strTitle + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");

            excelDoc.Write("<Row>\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"5\" ss:StyleID=\"Normal\"><Data ss:Type=\"String\"> Tổng số dòng: " + source.Rows.Count.ToString() + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");

            excelDoc.Write("<Row>\r\n ");
            for (int x = 0; x < source.Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Columns[x].Caption);
                excelDoc.Write("</Data></Cell>\r\n ");
            }
            excelDoc.Write("</Row>\r\n ");
            foreach (DataRow x in source.Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output

                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "

                for (int y = 0; y < source.Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  

                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000

                            //The Following Code puts the date stored in XMLDate 

                            //to the format above

                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date

                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }
        public static void exportDataToExcel(DataTable source, string fileName)
        {

            System.IO.StreamWriter excelDoc;

            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n" +
                  "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n " +
                  "xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n " +
                  "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n " +
                  "<Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n " +
                //"<Alignment ss:Vertical="Bottom"/>
                  "<Borders/>\r\n " +
                //"<Font/>\r\n " +
                  "<Font ss:FontName=\".VnTime\"/>\r\n" +
                  "<Interior/>\r\n " +
                  "<NumberFormat/>\r\n " +
                  "<Protection/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n" +
                  "<Font ss:FontName=\"Times New Roman\"/>\r\n" +
                  "<Interior ss:Color=\"#CCFFCC\" ss:Pattern=\"Solid\"/>\r\n" +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"StringLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Decimal\">\r\n " +
                  "<NumberFormat ss:Format=\"#,##0.0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n " +
                  "<NumberFormat ss:Format=\"0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"DateLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"dd/MM/yyyy;@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Title\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Font x:Family=\"Swiss\" ss:Size=\"14\" ss:Bold=\"1\"/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Normal\">\r\n " +
                  "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "</Style>\r\n " +
                  "</Styles>\r\n ";

            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;

            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">\r\n ");
            excelDoc.Write("<Table x:FullColumns=\"1\" x:FullRows=\"1\" >");

            for (int x = 0; x < source.Columns.Count; x++)
            {
                excelDoc.Write("<Column ss:Width=\"100\" ss:AutoFitWidth=\"1\" />\r\n ");
            }
            excelDoc.Write("<Row>\r\n ");
            for (int x = 0; x < source.Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Columns[x].Caption);
                excelDoc.Write("</Data></Cell>\r\n ");
            }
            excelDoc.Write("</Row>\r\n ");
            foreach (DataRow x in source.Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output

                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "

                for (int y = 0; y < source.Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  

                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000

                            //The Following Code puts the date stored in XMLDate 

                            //to the format above

                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date

                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }

        public static void exportDataToExcel(DataSet dssource, string fileName)
        {
            System.IO.StreamWriter excelDoc;
            excelDoc = new System.IO.StreamWriter(fileName);
            string excelContent = "";
            const string startExcelXML = "<xml version>\r\n" +
                  "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n " +
                  "xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n " +
                  "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n " +
                  "<Styles>\r\n " +
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n " +
                //"<Alignment ss:Vertical="Bottom"/>
                  "<Borders/>\r\n " +
                //"<Font/>\r\n " +
                  "<Font ss:FontName=\".VnTime\"/>\r\n" +
                  "<Interior/>\r\n " +
                  "<NumberFormat/>\r\n " +
                  "<Protection/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"BoldColumn\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n" +
                  "<Font ss:FontName=\"Times New Roman\"/>\r\n" +
                  "<Interior ss:Color=\"#CCFFCC\" ss:Pattern=\"Solid\"/>\r\n" +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"StringLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Decimal\">\r\n " +
                  //"<NumberFormat ss:Format=\"#,###0.0\"/>\r\n " +
                  "<NumberFormat ss:Format=\"#,##0.0###\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Integer\">\r\n " +
                  "<NumberFormat ss:Format=\"0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"DateLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"dd/MM/yyyy;@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Title\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Font x:Family=\"Swiss\" ss:Size=\"14\" ss:Bold=\"1\"/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Normal\">\r\n " +
                  "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "</Style>\r\n " +
                  "</Styles>\r\n ";

            const string endExcelXML = "</Workbook>";
            excelDoc.Write(startExcelXML);

            foreach (DataTable source in dssource.Tables)
            {

                int rowCount = 0;
                int sheetCount = 1;

                excelDoc.Write("<Worksheet ss:Name=\"" + source.TableName + "\">\r\n ");
                excelDoc.Write("<Table x:FullColumns=\"1\" x:FullRows=\"1\" >");

                for (int x = 0; x < source.Columns.Count; x++)
                {
                    excelDoc.Write("<Column ss:Width=\"100\" ss:AutoFitWidth=\"1\" />\r\n ");
                }
                excelDoc.Write("<Row>\r\n ");
                for (int x = 0; x < source.Columns.Count; x++)
                {
                    excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                    excelDoc.Write(source.Columns[x].Caption);
                    excelDoc.Write("</Data></Cell>\r\n ");
                }
                excelDoc.Write("</Row>\r\n ");
                foreach (DataRow x in source.Rows)
                {
                    rowCount++;
                    //if the number of rows is > 64000 create a new page to continue output

                    if (rowCount == 64000)
                    {
                        rowCount = 0;
                        sheetCount++;
                        excelDoc.Write("</Table>");
                        excelDoc.Write(" </Worksheet>");
                        excelDoc.Write("<Worksheet ss:Name=\"" + source.TableName + "_" + sheetCount + "\">\r\n ");
                        excelDoc.Write("<Table>");
                    }
                    excelDoc.Write("<Row>"); //ID=" + rowCount + "

                    for (int y = 0; y < source.Columns.Count; y++)
                    {
                        System.Type rowType;
                        rowType = x[y].GetType();
                        switch (rowType.ToString())
                        {
                            case "System.String":
                                string XMLstring = x[y].ToString();
                                XMLstring = XMLstring.Trim();
                                XMLstring = XMLstring.Replace("&", "&");
                                XMLstring = XMLstring.Replace(">", ">");
                                XMLstring = XMLstring.Replace("<", "<");
                                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                               "<Data ss:Type=\"String\">");
                                excelDoc.Write(XMLstring);
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.DateTime":
                                //Excel has a specific Date Format of YYYY-MM-DD followed by  

                                //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000

                                //The Following Code puts the date stored in XMLDate 

                                //to the format above

                                DateTime XMLDate = (DateTime)x[y];
                                string XMLDatetoString = ""; //Excel Converted Date

                                XMLDatetoString = XMLDate.Year.ToString() +
                                     "-" +
                                     (XMLDate.Month < 10 ? "0" +
                                     XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                     "-" +
                                     (XMLDate.Day < 10 ? "0" +
                                     XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                     "T" +
                                     (XMLDate.Hour < 10 ? "0" +
                                     XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                     ":" +
                                     (XMLDate.Minute < 10 ? "0" +
                                     XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                     ":" +
                                     (XMLDate.Second < 10 ? "0" +
                                     XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                     ".000";
                                excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                             "<Data ss:Type=\"DateTime\">");
                                excelDoc.Write(XMLDatetoString);
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Boolean":
                                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                            "<Data ss:Type=\"String\">");
                                excelDoc.Write(x[y].ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                        "<Data ss:Type=\"Number\">");
                                excelDoc.Write(x[y].ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Decimal":
                            case "System.Double":
                                excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                      "<Data ss:Type=\"Number\">");
                                excelDoc.Write(x[y].ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.DBNull":
                                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                      "<Data ss:Type=\"String\">");
                                excelDoc.Write("");
                                excelDoc.Write("</Data></Cell>");
                                break;
                            default:
                                throw (new Exception(rowType.ToString() + " not handled."));
                        }
                    }
                    excelDoc.Write("</Row>");
                }
                excelDoc.Write("</Table>");
                excelDoc.Write(" </Worksheet>");
            }

            //
            excelDoc.Write(endExcelXML);
            excelContent = excelDoc.ToString();
            excelDoc.Close();
        }

        public static void exportToExcelForEmployeeList(string strTitle, string branchName, string departmentName, DataSet source, string fileName)
        {

            System.IO.StreamWriter excelDoc;

            excelDoc = new System.IO.StreamWriter(fileName);
            const string startExcelXML = "<xml version>\r\n" +
                  "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n " +
                  "xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                  "xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n " +
                  "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n " +
                  "<Styles>\r\n " +

                  //Định dạng mặc định
                  "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                  "<Alignment ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Borders/>\r\n " +
                  "<Font/>\r\n " +
                  "<Interior/>\r\n " +
                  "<NumberFormat/>\r\n " +
                  "<Protection/>\r\n " +
                  "</Style>\r\n " +
                //Định dạng cho tiêu đề
                  "<Style ss:ID=\"BoldColumn\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n" +
                  "<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n " +
                  "<Interior ss:Color=\"#CCFFCC\" ss:Pattern=\"Solid\"/>\r\n" +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +

                  //Định dạng
                  "<Style ss:ID=\"StringLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +

                  //Định dạng kiểu số thập phân 
                  "<Style ss:ID=\"Decimal\">\r\n " +
                  "<NumberFormat ss:Format=\"#,##0.0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +

                    //Định dạng kiểu số thập phân 1
                  "<Style ss:ID=\"Decimal1\">\r\n " +
                  "<NumberFormat ss:Format=\"#0.00\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +

                    //Định dạng kiểu số thập phân 2
                  "<Style ss:ID=\"Decimal2\">\r\n " +
                  "<NumberFormat ss:Format=\"#,##0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +

                //Định dạng kiểu số nguyên
                  "<Style ss:ID=\"Integer\">\r\n " +
                  "<NumberFormat ss:Format=\"0\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +

                  //Định dạng kiểu ngày tháng
                  "<Style ss:ID=\"DateLiteral\">\r\n " +
                  "<NumberFormat ss:Format=\"dd/MM/yyyy;@\"/>\r\n " +
                  "<Borders>\r\n" +
                  "<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>\r\n" +
                  "</Borders>\r\n" +
                  "</Style>\r\n " +

                  //Định dạng tiêu đề
                  "<Style ss:ID=\"Title\">\r\n " +
                  "<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "<Font x:Family=\"Swiss\" ss:Size=\"14\" ss:Bold=\"1\"/>\r\n " +
                  "</Style>\r\n " +
                  "<Style ss:ID=\"Normal\">\r\n " +
                  "<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Bottom\"/>\r\n " +
                  "</Style>\r\n " +
                  "</Styles>\r\n ";

            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 1;

            excelDoc.Write(startExcelXML);
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">\r\n ");
            excelDoc.Write("<Table x:FullColumns=\"1\" x:FullRows=\"1\" >");

            for (int x = 0; x < source.Tables[0].Columns.Count; x++)
            {


                if (source.Tables[0].Columns[x].Caption == "STT")
                    excelDoc.Write("<Column  ss:Width=\"30\" />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Số thẻ")
                    excelDoc.Write("<Column  ss:Width=\"40\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Họ và tên")
                    excelDoc.Write("<Column  ss:Width=\"100\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Tên")
                    excelDoc.Write("<Column  ss:Width=\"40\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Họ và đệm")
                    excelDoc.Write("<Column  ss:Width=\"90\" />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Ngày sinh")
                    excelDoc.Write("<Column  ss:Width=\"70\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Giới tính")
                    excelDoc.Write("<Column  ss:Width=\"60\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Quốc tịch")
                    excelDoc.Write("<Column  ss:Width=\"70\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Tôn giáo")
                    excelDoc.Write("<Column  ss:Width=\"60\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "TTHN")
                    excelDoc.Write("<Column  ss:Width=\"90\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "CMTND")
                    excelDoc.Write("<Column  ss:Width=\"70\" />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Ngày cấp CMT")
                    excelDoc.Write("<Column  ss:Width=\"90\" />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Nơi cấp CMT")
                    excelDoc.Write("<Column  ss:Width=\"90\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Số sở BH")
                    excelDoc.Write("<Column  ss:Width=\"90\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "Lương")
                    excelDoc.Write("<Column  ss:Width=\"70\"  />\r\n ");
                else if (source.Tables[0].Columns[x].Caption == "HSL")
                    excelDoc.Write("<Column  ss:Width=\"30\"  />\r\n ");
                else
                    excelDoc.Write("<Column  ss:Width=\"100\" ss:AutoFitWidth=\"1\" />\r\n ");


            }
            // 1 dòng trắng
            excelDoc.Write("<Row ss:Height=\"18\">\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"8\" ss:StyleID=\"Title\"><Data ss:Type=\"String\"></Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");
            // Dòng tiêu đề (DANH SÁCH NHÂN VIÊN)
            excelDoc.Write("<Row ss:Height=\"18\">\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"8\" ss:StyleID=\"Title\"><Data ss:Type=\"String\">" + strTitle + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");
            // Đơn vị
            excelDoc.Write("<Row>\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"8\" ss:StyleID=\"Normal\"><Data ss:Type=\"String\"> Đơn vị: " + branchName.ToString() + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");
            // Phòng ban
            excelDoc.Write("<Row>\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"8\" ss:StyleID=\"Normal\"><Data ss:Type=\"String\"> Phòng ban: " + departmentName.ToString() + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");
            // 1 dòng trắng
            excelDoc.Write("<Row>\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"8\" ss:StyleID=\"Normal\"><Data ss:Type=\"String\"></Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");
            // Tổng số nhân viên
            excelDoc.Write("<Row ss:Height=\"16\">\r\n ");
            excelDoc.Write("<Cell ss:Index=\"1\" ss:MergeAcross=\"8\" ss:StyleID=\"Normal\"><Data ss:Type=\"String\"> Tổng số nhân viên: " + source.Tables[0].Rows.Count.ToString() + "</Data></Cell> \r\n");
            excelDoc.Write("</Row>\r\n ");

            excelDoc.Write("<Row>\r\n ");

            for (int x = 0; x < source.Tables[0].Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Tables[0].Columns[x].Caption);
                excelDoc.Write("</Data></Cell>\r\n ");
            }

            excelDoc.Write("</Row>\r\n ");
            foreach (DataRow x in source.Tables[0].Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output

                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "

                for (int y = 0; y < source.Tables[0].Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":

                            //Excel has a specific Date Format of YYYY-MM-DD followed by  
                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                            //The Following Code puts the date stored in XMLDate 
                            //to the format above

                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date

                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            //Xét định dạng số thập phân theo từng trường hợp
                            if (source.Tables[0].Columns[y].Caption == "HSL")
                                excelDoc.Write("<Cell ss:StyleID=\"Decimal1\">" +
                                      "<Data ss:Type=\"Number\">");
                            else if (source.Tables[0].Columns[y].Caption == "Lương")
                                excelDoc.Write("<Cell ss:StyleID=\"Decimal2\">" +
                                      "<Data ss:Type=\"Number\">");
                            else
                                excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                      "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " Not Handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            excelDoc.Write(endExcelXML);
            excelDoc.Close();
        }
    }

    public class UpdateUI
    {
        public static string GetString(string _name)
        {
            return Program.m_ResourceManager.GetString(_name);
        }
        public static void Load_ToolStrip(ref ToolStrip tlS)
        {
            foreach (ToolStripItem itm in tlS.Items)
            {
                if (itm.GetType().FullName.Contains("System.Windows.Forms.ToolStripButton"))
                    if (itm.Tag != null)
                        itm.Text = Program.m_ResourceManager.GetString(itm.Tag.ToString());
                    else
                        itm.Text = itm.Text;

                if (itm.GetType().FullName.Contains("System.Windows.Forms.ToolStripLabel"))
                    if (itm.Tag != null)
                        itm.Text = Program.m_ResourceManager.GetString(itm.Tag.ToString());
                    else
                        itm.Text = itm.Text;

                if (itm.GetType().FullName.Contains("System.Windows.Forms.ToolStripButton")
                    && itm.DisplayStyle == System.Windows.Forms.ToolStripItemDisplayStyle.Image)
                    if (itm.Tag != null)
                        itm.ToolTipText = Program.m_ResourceManager.GetString(itm.Tag.ToString());
                    else
                        itm.ToolTipText = itm.ToolTipText;

            }
        }
        public static void Load_ContextMenuStrip(ref ContextMenuStrip cms)
        {
            foreach (ToolStripItem item in cms.Items)
            {
                if (item.Tag != null)
                    item.Text = Program.m_ResourceManager.GetString(item.Tag.ToString());
                else
                    item.Text = item.Text;
            }
        }
        public static void Load_GroupBox(ref GroupBox gb)
        {
            if (gb.Tag != null)
                gb.Text = Program.m_ResourceManager.GetString(gb.Tag.ToString());
            else
                gb.Text = gb.Text;
            foreach (Control ctl in gb.Controls)
            {
                if (ctl.ToString().Contains("System.Windows.Forms.Label"))
                    if (ctl.Tag.ToString().Trim() != "")
                        ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                    else
                        ctl.Text = ctl.Text;

                if (ctl.ToString().Contains("System.Windows.Forms.Button"))
                    if (ctl.Tag != null)
                        ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                    else
                        ctl.Text = ctl.Text;
                if (ctl.ToString().Contains("System.Windows.Forms.CheckBox"))
                    if (ctl.Tag != null)
                        ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                    else
                        ctl.Text = ctl.Text;


            }
        }
        public static void Load_Panel(ref Panel P)
        {
            foreach (Control ctl in P.Controls)
            {
                if (ctl.ToString().Contains("System.Windows.Forms.Label"))
                    if (ctl.Tag != null)
                        if (ctl.Tag.ToString() == "duongthang")
                            ctl.Text = ctl.Text;
                        else
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                    else
                        ctl.Text = ctl.Text;
                if (ctl.ToString().Contains("System.Windows.Forms.Button"))
                    if (ctl.Tag != null)
                        ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                    else
                        ctl.Text = ctl.Text;
                if (ctl.ToString().Contains("System.Windows.Forms.RadioButton"))
                    if (ctl.Tag != null)
                        ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                    else
                        ctl.Text = ctl.Text;
                if (ctl.ToString().Contains("System.Windows.Forms.CheckBox"))
                    if (ctl.Tag != null)
                        ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                    else
                        ctl.Text = ctl.Text;
            }
        }

        public static void Load_MenuStrip(ref MenuStrip msp)
        {
            foreach (ToolStripItem item in msp.Items)
            {
                if (item.Tag != null)
                    item.Text = Program.m_ResourceManager.GetString(item.Tag.ToString());
                else
                    item.Text = item.Text;
            }
        }
        public static void Load_ToolStripMenuItem(ref ToolStripMenuItem tsmi)
        {
            foreach (ToolStripItem dropItem in tsmi.DropDownItems)
            {
                if (dropItem.Tag != null)
                    dropItem.Text = Program.m_ResourceManager.GetString(dropItem.Tag.ToString());
                else
                    dropItem.Text = dropItem.Text;
            }
        }
        public static void Load_TabControl(ref TabControl tc)
        {
            foreach (TabPage tp in tc.TabPages)
            {
                if (tp.GetType().FullName.Contains("System.Windows.Forms.TabPage"))
                    if (tp.Tag != null)
                        tp.Text = Program.m_ResourceManager.GetString(tp.Tag.ToString());
                    else
                        tp.Text = tp.Text;
                foreach (Control ctl in tp.Controls)
                {
                    if (ctl.ToString().Contains("System.Windows.Forms.Label"))
                        if (ctl.Tag != null)
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;
                    if (ctl.ToString().Contains("System.Windows.Forms.Button"))
                        if (ctl.Tag != null)
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;
                    if (ctl.ToString().Contains("System.Windows.Forms.RadioButton"))
                        if (ctl.Tag != null)
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;
                    if (ctl.ToString().Contains("System.Windows.Forms.CheckBox"))
                        if (ctl.Tag != null)
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;

                }
            }

        }
        public static void Load_TabControl1(ref TabControl tc)
        {
            foreach (TabPage tp in tc.TabPages)
            {
                if (tp.GetType().FullName.Contains("System.Windows.Forms.TabPage"))
                    if (tp.Tag != null)
                        tp.Text = Program.m_ResourceManager.GetString(tp.Tag.ToString());
                    else
                        tp.Text = tp.Text;
                foreach (Control ctl in tp.Controls)
                {
                    if (ctl.ToString().Contains("System.Windows.Forms.Label"))
                        if (ctl.Tag.ToString() != "")
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;
                    if (ctl.ToString().Contains("System.Windows.Forms.Button"))
                        if (ctl.Tag != null)
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;
                    if (ctl.ToString().Contains("System.Windows.Forms.RadioButton"))
                        if (ctl.Tag != null)
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;
                    if (ctl.ToString().Contains("System.Windows.Forms.CheckBox"))
                        if (ctl.Tag != null)
                            ctl.Text = Program.m_ResourceManager.GetString(ctl.Tag.ToString());
                        else
                            ctl.Text = ctl.Text;

                }
            }

        }
    }

    public static class CheckTextValid
    {
        public static bool IsDateTime(string value, string format)
        {
            DateTime dateVal;

            return DateTime.TryParseExact(value, format, null, System.Globalization.DateTimeStyles.None, out  dateVal);
        }
        public static bool IsDateTime(string value, string format, out DateTime returnValue)
        {
            returnValue = DateTime.MinValue;
            return DateTime.TryParseExact(value, format, null, System.Globalization.DateTimeStyles.None, out  returnValue);
        }
        public static bool IsEmpty(string value)
        {
            return (value.Trim().Length == 0);
        }
        public static bool IsDecimal(string value)
        {
            Decimal DecVal = 0;
            return Decimal.TryParse(value, out DecVal);
        }
        public static bool IsInteger(string value)
        {
            int intVal = 0;
            return Int32.TryParse(value, out intVal);
        }
    }

    public class NumberToWordsConvertor
    {
        // Single-digit and small number names
        private string[] _smallNumbers = new string[] { "zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "tourteen", "tifteen", "sixteen", "seventeen", "eighteen", "nineteen" };

        // Tens number names from twenty upwards
        private string[] _tens = new string[] { "", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety" };

        // Scale number names for use during recombination
        private string[] _scaleNumbers = new string[] { "", "thousand", "million", "billion" };



        // Converts an integer value into English words
        public string NumberToWords(int number)
        {
            // Zero rule
            if (number == 0)
                return _smallNumbers[0];

            // Array to hold four three-digit groups
            int[] digitGroups = new int[4];

            // Ensure a positive number to extract from
            int positive = Math.Abs(number);

            // Extract the three-digit groups
            for (int i = 0; i < 4; i++)
            {
                digitGroups[i] = positive % 1000;
                positive /= 1000;
            }

            // Convert each three-digit group to words
            string[] groupText = new string[4];

            for (int i = 0; i < 4; i++)
                groupText[i] = ThreeDigitGroupToWords(digitGroups[i]);

            // Recombine the three-digit groups
            string combined = groupText[0];
            bool appendAnd;

            // Determine whether an 'and' is needed
            appendAnd = (digitGroups[0] > 0) && (digitGroups[0] < 100);

            // Process the remaining groups in turn, smallest to largest
            for (int i = 1; i < 4; i++)
            {
                // Only add non-zero items
                if (digitGroups[i] != 0)
                {
                    // Build the string to add as a prefix
                    string prefix = groupText[i] + " " + _scaleNumbers[i];

                    if (combined.Length != 0)
                        prefix += appendAnd ? " and " : ", ";

                    // Opportunity to add 'and' is ended
                    appendAnd = false;

                    // Add the three-digit group to the combined string
                    combined = prefix + combined;
                }
            }

            // Negative rule
            if (number < 0)
                combined = "Negative " + combined;

            return combined;
        }



        // Converts a three-digit group into English words
        private string ThreeDigitGroupToWords(int threeDigits)
        {
            // Initialise the return text
            string groupText = "";

            // Determine the hundreds and the remainder
            int hundreds = threeDigits / 100;
            int tensUnits = threeDigits % 100;

            // Hundreds rules
            if (hundreds != 0)
            {
                groupText += _smallNumbers[hundreds] + " hundred";

                if (tensUnits != 0)
                    groupText += " and ";
            }

            // Determine the tens and units
            int tens = tensUnits / 10;
            int units = tensUnits % 10;

            // Tens rules
            if (tens >= 2)
            {
                groupText += _tens[tens];
                if (units != 0)
                    groupText += " " + _smallNumbers[units];
            }
            else if (tensUnits != 0)
                groupText += _smallNumbers[tensUnits];

            return groupText;
        }

        public static string Group32StrX(string num)
        {
            string[] No = { "không", "một", "hai", "ba", "bốn", "năm", "sáu", "bảy", "tám", "chín" };
            string kq, tram, chuc, donvi;
            // Trăm
            if (num.Substring(0, 1) == "0")
            { tram = ""; }
            else
            { tram = No[Convert.ToByte(num.Substring(0, 1))] + " trăm "; }
            // Chục
            switch (num.Substring(1, 1))
            {
                case "0": if (num.Substring(2, 1) != "0" && num.Substring(0, 1) != "0")
                    {
                        chuc = "linh ";
                    }
                    else { chuc = ""; }; break;
                case "1": chuc = "mười "; break;
                default:
                    chuc = No[Convert.ToByte(num.Substring(1, 1))] + " mươi "; break;
            }
            // Đơn vị
            switch (num.Substring(2, 1))
            {
                case "0": donvi = ""; break;
                case "1": if ((num.Substring(1, 1) == "0") || (num.Substring(1, 1) == "1"))
                    {
                        donvi = "một";
                    }
                    else
                    {
                        donvi = "mốt";
                    }; break;
                case "5":
                    if (num.Substring(1, 1) != "0")
                    {
                        donvi = "lăm";
                    }
                    else
                    {
                        donvi = "năm";
                    }; break;
                default:
                    donvi = No[Convert.ToByte(num.Substring(2, 1))]; break;
            }
            kq = tram + chuc + donvi;
            return kq;
        }
        public static string IntNum2Str(string num)
        {
            string[] Cap = { "", " nghìn ", " triệu ", " tỷ ", " nghìn tỷ ", " triệu tỷ ", " tỷ tỷ ", " nghìn tỷ tỷ " };
            string kq = "", str = num, g3, kqtg;
            int caps = 0;
            while (str.Length > 3)
            {
                g3 = str.Substring(str.Length - 3, 3);
                str = str.Substring(0, str.Length - 3);
                if (g3 != "000")
                { kqtg = Group32StrX(g3) + Cap[Convert.ToByte(caps)]; }
                else { kqtg = ""; }
                kq = kqtg + kq;
                caps++;
            }
            //Chuẩn bị trước khi sử dụng hàm Group32Str1
            while (str.Length < 3)
            { str = "0" + str; }

            if ((str == "000") && (num.Length <= 3))
            { kqtg = "không"; }
            else
            { kqtg = Group32StrX(str) + Cap[Convert.ToByte(caps)]; }
            kq = kqtg + kq;
            return kq;
        }
        public static string FracNum2Str(string num)
        {
            string[] No = { "không", "một", "hai", "ba", "bốn", "năm", "sáu", "bảy", "tám", "chín" };
            string kq = "";
            for (int i = 0; i < num.Length; i++)
            {
                kq += No[Convert.ToByte(num.Substring(i, 1))] + " ";
            }
            return kq;
        }
        public static string No2Str(string num)
        {
            string intstr, fracstr, am;
            // Xử lý khi là số âm
            if (num.Substring(0, 1) == "-")
            {
                am = "âm ";
                num = num.Replace("-", "");
            }
            else { am = ""; }

            string[] str = num.Split('.');
            // Số quá lớn nhiều hơn 27 chữ số
            if (str[0].Length > 28)
                throw new Exception("Số lớn quá không hiển thị được! ");

            string s;
            // Xử lý phần số, nếu là có số thập phân hoặc không
            if (str.Length == 2)
            {
                intstr = IntNum2Str(str[0]);
                if (str[1].Length <= 2)
                {
                    if (str[1].Substring(0, 1) == "0")
                    {
                        fracstr = FracNum2Str(str[1]);
                    }
                    else
                    {
                        fracstr = IntNum2Str(str[1]);
                    }
                }
                else
                {
                    fracstr = FracNum2Str(str[1]);
                }
                s = (intstr + " phẩy " + fracstr);
            }
            else
            {
                intstr = IntNum2Str(str[0]);
                s = intstr;
            }
            s = am + s;
            s = s.Trim();
            string chuhoa = s.Substring(0, 1).ToUpper();
            s = s.Substring(1, s.Length - 1);
            return (chuhoa + s);
        }
        public static string NumNormalize(string num)
        {
            char ThousandSpace = ',';
            char PointScape = '.';
            string[] s = num.Split('.');
            string g3, str = "";
            while (s[0].Length > 3)
            {
                g3 = s[0].Substring(s[0].Length - 3, 3);
                s[0] = s[0].Substring(0, s[0].Length - 3);
                str = ThousandSpace + g3 + str;
            }
            str = s[0] + str;
            if (s.Length == 2)
            {
                str = (str + PointScape + s[1]);
            }
            return str.Replace("-,", "-");
        }
        public static string NumberToString(string no)
        {
            // xử lý trường hợp dáu phảy thay cho dấu chấm
            if (no.IndexOf(",", 0, 1) != 0 || no.IndexOf(",", 0, 2) != 0) { no = no.Replace(",", "."); }
            if (no.Contains(".00")) { no = no.Replace(".00", ""); }
            // Xoá các ký tự trắng ở đầu và cuối
            no = no.Trim();
            // Xử lý khi nó là chữ chứ không phải là số
            if (no == "0") return "Không";
            if (no == "") return "Không";
            double val;
            try
            {
                val = Convert.ToDouble(no);
            }
            catch
            {
                throw new Exception("Đây không phải là số");

            }
            // tiêu diệt các số không
            while (no.Substring(0, 1) == "0")
            {
                no = no.Substring(1, no.Length - 1);
            }

            if (no.IndexOf(".", 0, 1) != -1)
            {
                while (no.Substring(no.Length - 1, 1) == "0")
                {
                    no = no.Substring(0, no.Length - 1);
                }
            }
            no = No2Str(no);
            var s = no.Split(' ');
            var str = "";
            foreach (string s1 in s)
            {
                if (s1.Trim().Length > 0)
                    str += s1.Trim() + " ";
            }
            return str.Trim();
        }
    }


    public class DateTextBox
    {
        public static string CS_DISPLAY_DATE_FORMAT = "dd/MM/yyyy";
        public static string CS_EDIT_DATE_FORMAT = "dd/MM/yyyy";

        public static System.DateTime Date_ParseEx(string sValue)
        {
            System.DateTime mDate = DateTime.MinValue;
            Date_TryParseEx(sValue, ref mDate);
            return mDate;
        }

        public static bool Date_TryParseEx(string sValue, ref System.DateTime dReturn)
        {
            if (System.DateTime.TryParseExact(sValue, CS_EDIT_DATE_FORMAT, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AdjustToUniversal, out dReturn))
            {
                return true;
            }
            else
            {
                if (System.DateTime.TryParse(sValue, out dReturn))
                {
                    return true;
                }
            }
            return false;
        }

        public static void Date_FixString(ref string sValue)
        {
            System.DateTime tempresult1 = DateTime.MinValue;
            if (!(System.DateTime.TryParse(sValue, out tempresult1)) || sValue.Length > 4)
            {
                sValue = sValue.Replace(" ", "/");
                if (sValue.Length == 8)
                {
                    sValue = sValue.Substring(0, 2) + "/" + sValue.Substring(2, 2) + "/" + sValue.Substring(4, 4);
                }
                if (sValue.Length == 6)
                {
                    DateTime dt = DateTime.Parse("01/01/" + sValue.Substring(4, 2));
                    sValue = sValue.Substring(0, 2) + "/" + sValue.Substring(2, 2) + "/" + dt.Year;  //+ sValue.Substring(4, 2);
                }
                else if (sValue.Length == 4)
                {
                    sValue = sValue.Substring(0, 2) + "/" + sValue.Substring(2, 2);
                    sValue = sValue + "/" + System.DateTime.Today.Year;
                }
                else if (sValue.Length == 5 && sValue.Substring(2, 1) == "/")
                {
                    sValue = sValue + "/" + System.DateTime.Today.Year;

                }
                else if (sValue.Length == 2)
                {
                    if (long.Parse(sValue) < 32)
                    {
                        sValue += "/" + System.DateTime.Today.Month.ToString().PadLeft(2, '0');
                        sValue = sValue + "/" + System.DateTime.Today.Year;
                    }
                }
            }
        }

        private TextBox objTextBox;
        private bool m_bClearError = true;

        protected DateTextBox()
        {

        }

        public bool AutoClearError
        {
            get
            {
                return m_bClearError;
            }
            set
            {
                m_bClearError = value;
            }
        }

        public string ControlName
        {
            get
            {
                return objTextBox.Name;
            }
        }

        public DateTextBox(TextBox tb)
        {
            this.objTextBox = tb;
            objTextBox.Enter += objTextBox_Enter;
            objTextBox.KeyPress += TextBox_KeyPress;
            objTextBox.MouseEnter += objTextBox_MouseEnter;
            objTextBox.MouseLeave += objTextBox_MouseLeave;
            objTextBox.TextChanged += objTextBox_TextChanged;
            objTextBox.Validating += TextBox_Validating;

            tb.MaxLength = 10;
            tb.CharacterCasing = CharacterCasing.Upper;
            //

            PictureBox btn = new PictureBox();
            //btn.ImageAlign = ContentAlignment.MiddleCenter;
            //btn.FlatStyle = FlatStyle.Flat;
            //btn.FlatAppearance.BorderSize = 0;
            btn.TabStop = false;
            btn.Image = Resources.btnCalendar;
            btn.Size = new Size(tb.Size.Height - 2, tb.Size.Height - 2);
            btn.Location = new Point(tb.Width - btn.Width - 3, -2);
            if (!tb.Controls.ContainsKey(btn.Name))
                tb.Controls.Add(btn);
            //btn.Enabled = false;
            //tb.CreateGraphics().DrawImage(Resources.check, new Point(tb.Width - 16, -2));
        }

        private void objTextBox_Enter(object sender, System.EventArgs e)
        {
            try
            {
                if (objTextBox.ReadOnly)
                {
                    return;
                }
                DateTime value = DateTime.MinValue;
                if (Date_TryParseEx(objTextBox.Text, ref value))
                {
                    objTextBox.Text = value.ToString(CS_EDIT_DATE_FORMAT);
                }

                objTextBox.SelectAll();
            }
            catch (Exception ex)
            {
                //Message_Error(ex);
            }
        }

        private void TextBox_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            try
            {
                if (e.Handled)
                {
                    return;
                }
                bool bHandled = false;
                switch (System.Convert.ToInt32(e.KeyChar))
                {
                    //case 13: //Enter
                    //    if (!objTextBox.Multiline)
                    //    {
                    //        e.Handled = true;
                    //            SendKeys.SendWait("{TAB}");
                    //        return;
                    //    }
                    //    break;
                    case 8: //Backspace
                        break;
                    default:
                        string sValidChar = "0123456789/-";
                        if (sValidChar != "")
                        {
                            bHandled = (sValidChar.IndexOf(e.KeyChar) < 0);
                        }
                        break;
                }

                e.Handled = bHandled;

            }
            catch (Exception ex)
            {
                //Throw ex
                //Message_Error(ex);
            }
        }

        private void objTextBox_MouseEnter(object sender, System.EventArgs e)
        {
            //m_bMouseOver = true;
        }

        private void objTextBox_MouseLeave(object sender, System.EventArgs e)
        {
            //m_bMouseOver = false;
        }

        private void objTextBox_TextChanged(object sender, System.EventArgs e)
        {
            //Control_SetError(objTextBox, "");
        }

        private void TextBox_Validating(object sender, System.ComponentModel.CancelEventArgs e)
        {
            try
            {
                if (objTextBox.ReadOnly)
                {
                    return;
                }

                string sValue = objTextBox.Text.Trim();

                bool bWrong = false;
                string sMessage = "";

                if (sValue.Length > 0)
                {

                    Date_FixString(ref sValue);
                    System.DateTime mDate = DateTime.MinValue;
                    if (!(Date_TryParseEx(sValue, ref mDate)))
                    {
                        bWrong = true;
                    }
                    else
                    {
                        //Object_SetValue(sValue, mDate)
                        objTextBox.Text = mDate.ToString(CS_DISPLAY_DATE_FORMAT); //CType(sValue, Date).ToString(cstDisplayDateFormat)
                    }
                    sMessage = "Please input Date value";

                    if (bWrong && m_bClearError)
                    {
                        //Auto clear error
                        objTextBox.Text = "";
                        return;
                    }
                }

                if (bWrong)
                {
                    e.Cancel = true;
                }
                else
                {
                    sMessage = "";
                }

                //Control_SetError(objTextBox, sMessage);

            }
            catch (Exception ex)
            {
                //Throw
                //Message_Error(ex);
            }
        }

    }

    public class DateTextBoxShort
    {
        public static string CS_DISPLAY_DATE_FORMAT = "MM/yyyy";
        public static string CS_EDIT_DATE_FORMAT = "dd/MM/yyyy";

        public static System.DateTime Date_ParseEx(string sValue)
        {
            System.DateTime mDate = DateTime.MinValue;
            Date_TryParseEx(sValue, ref mDate);
            return mDate;
        }

        public static bool Date_TryParseEx(string sValue, ref System.DateTime dReturn)
        {
            if (System.DateTime.TryParseExact(sValue, CS_EDIT_DATE_FORMAT, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AdjustToUniversal, out dReturn))
            {
                return true;
            }
            else
            {
                if (System.DateTime.TryParse(sValue, out dReturn))
                {
                    return true;
                }
            }
            return false;
        }

        public static void Date_FixString(ref string sValue)
        {
            System.DateTime tempresult1 = DateTime.MinValue;
            if (!(System.DateTime.TryParse(sValue, out tempresult1)) || sValue.Length > 4)
            {
                sValue = sValue.Replace(" ", "/");
                if (sValue.Length == 8)
                {
                    sValue = sValue.Substring(0, 2) + "/" + sValue.Substring(2, 2) + "/" + sValue.Substring(4, 4);
                }
                if (sValue.Length == 6)
                {
                    DateTime dt = DateTime.Parse("01/01/" + sValue.Substring(4, 2));
                    sValue = sValue.Substring(0, 2) + "/" + sValue.Substring(2, 2) + "/" + dt.Year;  //+ sValue.Substring(4, 2);
                }
                else if (sValue.Length == 4)
                {
                    sValue = sValue.Substring(0, 2) + "/" + sValue.Substring(2, 2);
                    sValue = sValue + "/" + System.DateTime.Today.Year;
                }
                else if (sValue.Length == 5 && sValue.Substring(2, 1) == "/")
                {
                    sValue = sValue + "/" + System.DateTime.Today.Year;

                }
                else if (sValue.Length == 2)
                {
                    if (long.Parse(sValue) < 32)
                    {
                        sValue += "/" + System.DateTime.Today.Month.ToString().PadLeft(2, '0');
                        sValue = sValue + "/" + System.DateTime.Today.Year;
                    }
                }
            }
        }

        private TextBox objTextBox;
        private bool m_bClearError = true;

        protected DateTextBoxShort()
        {

        }

        public bool AutoClearError
        {
            get
            {
                return m_bClearError;
            }
            set
            {
                m_bClearError = value;
            }
        }

        public string ControlName
        {
            get
            {
                return objTextBox.Name;
            }
        }

        public DateTextBoxShort(TextBox tb)
        {
            this.objTextBox = tb;
            objTextBox.Enter += objTextBox_Enter;
            objTextBox.KeyPress += TextBox_KeyPress;
            objTextBox.MouseEnter += objTextBox_MouseEnter;
            objTextBox.MouseLeave += objTextBox_MouseLeave;
            objTextBox.TextChanged += objTextBox_TextChanged;
            objTextBox.Validating += TextBox_Validating;

            tb.MaxLength = 7;
            tb.CharacterCasing = CharacterCasing.Upper;

            PictureBox btn = new PictureBox();
            //btn.ImageAlign = ContentAlignment.MiddleCenter;
            //btn.FlatStyle = FlatStyle.Flat;
            //btn.FlatAppearance.BorderSize = 0;
            btn.TabStop = false;
            btn.Image = Resources.btnCalendar;
            btn.Size = new Size(tb.Size.Height - 2, tb.Size.Height - 2);
            btn.Location = new Point(tb.Width - btn.Width - 3, -2);
            if (!tb.Controls.ContainsKey(btn.Name))
                tb.Controls.Add(btn);
        }

        public DateTextBoxShort(params TextBox[] tbs)
        {
            foreach (TextBox objTextBox in tbs)
            {
                objTextBox.Enter += objTextBox_Enter;
                objTextBox.KeyPress += TextBox_KeyPress;
                objTextBox.MouseEnter += objTextBox_MouseEnter;
                objTextBox.MouseLeave += objTextBox_MouseLeave;
                objTextBox.TextChanged += objTextBox_TextChanged;
                objTextBox.Validating += TextBox_Validating;

                objTextBox.MaxLength = 7;
                objTextBox.CharacterCasing = CharacterCasing.Upper;

                PictureBox btn = new PictureBox();
                //btn.ImageAlign = ContentAlignment.MiddleCenter;
                //btn.FlatStyle = FlatStyle.Flat;
                //btn.FlatAppearance.BorderSize = 0;
                btn.TabStop = false;
                btn.Image = Resources.btnCalendar;
                btn.Size = new Size(objTextBox.Size.Height - 2, objTextBox.Size.Height - 2);
                btn.Location = new Point(objTextBox.Width - btn.Width - 3, -2);
                if (!objTextBox.Controls.ContainsKey(btn.Name))
                    objTextBox.Controls.Add(btn);
            }
        }

        private void objTextBox_Enter(object sender, System.EventArgs e)
        {
            try
            {
                if (objTextBox.ReadOnly)
                {
                    return;
                }
                DateTime value = DateTime.MinValue;
                if (Date_TryParseEx("01/" + objTextBox.Text, ref value))
                {
                    objTextBox.Text = value.ToString(CS_DISPLAY_DATE_FORMAT);
                }

                objTextBox.SelectAll();
            }
            catch (Exception ex)
            {
                //Message_Error(ex);
            }
        }

        private void TextBox_KeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            try
            {
                if (e.Handled)
                {
                    return;
                }

                bool bHandled = false;
                switch (System.Convert.ToInt32(e.KeyChar))
                {
                    //case 13: //Enter
                    //    if (!objTextBox.Multiline)
                    //    {
                    //        e.Handled = true;
                    //        SendKeys.SendWait("{TAB}");
                    //        return;
                    //    }
                    //    break;
                    case 8: //Backspace
                        break;
                    default:
                        string sValidChar = "0123456789/-";
                        if (sValidChar != "")
                        {
                            bHandled = (sValidChar.IndexOf(e.KeyChar) < 0);
                        }
                        break;
                }

                e.Handled = bHandled;

            }
            catch (Exception ex)
            {
                //Throw ex
                //Message_Error(ex);
            }
        }

        private void objTextBox_MouseEnter(object sender, System.EventArgs e)
        {
            //m_bMouseOver = true;
        }

        private void objTextBox_MouseLeave(object sender, System.EventArgs e)
        {
            //m_bMouseOver = false;
        }

        private void objTextBox_TextChanged(object sender, System.EventArgs e)
        {
            //Control_SetError(objTextBox, "");
        }

        private void TextBox_Validating(object sender, System.ComponentModel.CancelEventArgs e)
        {
            try
            {
                if (objTextBox.ReadOnly)
                {
                    return;
                }
                string sValue = "";
                if (objTextBox.Text.Trim().Length > 0)
                {
                    if (objTextBox.Text.Contains("/"))
                        sValue = "01/" + objTextBox.Text.Trim();
                    else
                        sValue = "01" + objTextBox.Text.Trim();
                }
                bool bWrong = false;
                string sMessage = "";

                if (sValue.Length > 0)
                {

                    Date_FixString(ref sValue);
                    System.DateTime mDate = DateTime.MinValue;
                    if (!(Date_TryParseEx(sValue, ref mDate)))
                    {
                        bWrong = true;
                    }
                    else
                    {
                        //Object_SetValue(sValue, mDate)
                        objTextBox.Text = mDate.ToString(CS_DISPLAY_DATE_FORMAT); //CType(sValue, Date).ToString(cstDisplayDateFormat)
                    }
                    sMessage = "Please input Date value";

                    if (bWrong && m_bClearError)
                    {
                        //Auto clear error
                        objTextBox.Text = "";
                        return;
                    }
                }

                if (bWrong)
                {
                    e.Cancel = true;
                }
                else
                {
                    sMessage = "";
                }

                //Control_SetError(objTextBox, sMessage);

            }
            catch (Exception ex)
            {
                //Throw
                //Message_Error(ex);
            }
        }

    }

    public class DateCalculator
    {
        #region Private Members
        private int _Days;
        private int _Weeks;
        private int _Months;
        private int _Years;
        private int _TotalDays = 1;
        private int _TotalWeeks;
        private int _TotalMonths;
        private DateTime FromDate;
        private DateTime ToDate;
        DateTime CheckDate = new DateTime();
        private int[] MonthDays = new int[12] { 31, -1, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 };
        private int[] _MonthAccural = new int[12] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        private int _NumberOfLeapYears = 0;

        #endregion

        #region Proprietes
        public int Days
        {
            get { return _Days; }

        }

        public int Weeks
        {
            get { return _Weeks; }

        }

        public int Months
        {
            get { return _Months; }

        }

        public int Years
        {
            get { return _Years; }

        }

        public int TotalDays
        {
            get { return _TotalDays; }
        }

        public int TotalWeeks
        {
            get { return _TotalWeeks; }
        }

        public int TotalMonths
        {
            get { return _TotalMonths; }
        }

        public int[] MonthAccural
        {
            get { return _MonthAccural; }
        }

        public int NumberOfLeapYears
        {
            get { return _NumberOfLeapYears; }
        }
        #endregion

        #region Constructor
        public DateCalculator(DateTime _StartDate, DateTime _EndDate)
        {
            if (_StartDate != null && _EndDate != null)
            {
                if (_StartDate > _EndDate)
                {
                    this.FromDate = _EndDate;
                    this.ToDate = _StartDate;
                }
                else
                {
                    this.FromDate = _StartDate;
                    this.ToDate = _EndDate;
                }

            }
            else
            {
                throw new Exception("The value of Start or End date can't be null");
            }

        }
        #endregion

        #region Methods
        public void CalculateDateDifference()
        {
            bool Finish = false;
            DateTime TempDate = this.FromDate;
            int MonthsIndex = this.FromDate.Month - 1;
            int CurrentMonthDayes = this.MonthDays[MonthsIndex];
            this._TotalDays -= this.FromDate.Day;

            while (!Finish)
            {

                if (CurrentMonthDayes == -1)
                {
                    if (DateTime.IsLeapYear(TempDate.Year))
                    {
                        CheckDate = TempDate.AddDays(29);
                        TempDate = CheckDate;
                        this._NumberOfLeapYears++;

                    }
                    else
                    {
                        CheckDate = TempDate.AddDays(28);
                        TempDate = CheckDate;
                    }
                    this._MonthAccural[MonthsIndex]++;
                    this._Months++;
                }
                else
                {
                    CheckDate = TempDate.AddDays(CurrentMonthDayes);
                    TempDate = CheckDate;
                    this._MonthAccural[MonthsIndex]++;
                    this._Months++;
                }

                if (!CheckTerminationCondition())
                {
                    if (MonthsIndex == 11)
                        MonthsIndex = 0;
                    else
                        MonthsIndex++;

                    CurrentMonthDayes = this.MonthDays[MonthsIndex];
                }
                else
                {
                    Finish = true;
                    int Accumlator = 0;
                    for (int i = 0; i <= 11; i++)
                    {
                        if (i == 1)
                        {
                            Accumlator += this._MonthAccural[i] * 28;
                        }
                        else
                        {
                            Accumlator += this._MonthAccural[i] * this.MonthDays[i];
                        }
                    }
                    Accumlator += this._NumberOfLeapYears;

                    if (CheckDate > this.ToDate)
                    {
                        this._Months--;
                        this._MonthAccural[MonthsIndex]--;
                        Accumlator -= this.MonthDays[MonthsIndex];
                    }
                    DateTime AccumlatedEndDate = this.FromDate.AddDays(Accumlator);
                    this._Days = ((TimeSpan)this.ToDate.Subtract(AccumlatedEndDate)).Days;
                    this._TotalDays = Accumlator + this._Days;
                    this._TotalWeeks = this._TotalDays / 7;
                    this._TotalMonths = this._Months;

                    if ((this._Months / 12) > 0)
                    {
                        this._Years = this._Months / 12;
                        this._Months = this._Months % 12;
                    }
                    if ((this._Days / 7) > 0)
                    {
                        this._Weeks = this._Days / 7;
                        //this._Days = this._Days % 7;
                    }

                }


            }
        }
        #endregion

        #region Helper Methods
        private bool CheckTerminationCondition()
        {
            if (CheckDate >= this.ToDate)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

    }

    public class Report2Printer
    {
        int _currentPageIndex = 0;
        private IList<Stream> m_streams;
        bool _Landscape = false;
        bool _Rotate = false;

        public bool Rotate
        {
            get { return _Rotate; }
            set { _Rotate = value; }
        }
        public bool Landscape
        {
            get { return _Landscape; }
            set { _Landscape = value; }
        }
        public int CurrentPageIndex
        {
            get { return _currentPageIndex; }
            set { _currentPageIndex = value; }
        }

        // Routine to provide to the report renderer, in order to
        //    save an image for each page of the report.
        private Stream CreateStream(string name,
          string fileNameExtension, Encoding encoding,
          string mimeType, bool willSeek)
        {
            Stream stream = new FileStream(System.IO.Path.GetTempPath() + name +
               "." + fileNameExtension, FileMode.Create);
            m_streams.Add(stream);
            return stream;
        }

        // Export the given report as an EMF (Enhanced Metafile) file.
        public void Export(LocalReport report)
        {
            string deviceInfo = "<DeviceInfo>" +
              "  <OutputFormat>EMF</OutputFormat>";

            if (_Landscape)
                deviceInfo += "  <PageWidth>11in</PageWidth>" +
            "  <PageHeight>8.5in</PageHeight>";
            else
                deviceInfo += "  <PageWidth>8.5in</PageWidth>" +
                "  <PageHeight>11in</PageHeight>";

            deviceInfo += "  <MarginTop>0.00in</MarginTop>" +
              "  <MarginLeft>0.00in</MarginLeft>" +
              "  <MarginRight>0.00in</MarginRight>" +
              "  <MarginBottom>0.00in</MarginBottom>" +
              "</DeviceInfo>";

            Warning[] warnings;
            m_streams = new List<Stream>();
            report.Render("Image", deviceInfo, CreateStream,
               out warnings);
            foreach (Stream stream in m_streams)
                stream.Position = 0;
        }

        public void Export(LocalReport report, bool margin)
        {
            string deviceInfo = "<DeviceInfo>" +
              "  <OutputFormat>EMF</OutputFormat>";
            if (_Landscape)
                deviceInfo += "  <PageWidth>11in</PageWidth>" +
            "  <PageHeight>8.5in</PageHeight>";
            else
                deviceInfo += "  <PageWidth>8.5in</PageWidth>" +
                "  <PageHeight>11in</PageHeight>";
            if (margin)
                deviceInfo += "  <MarginTop>0.25in</MarginTop>" +
                 "  <MarginLeft>0.25in</MarginLeft>" +
                 "  <MarginRight>0.25in</MarginRight>" +
                 "  <MarginBottom>0.25in</MarginBottom>";
            else
                deviceInfo += "  <MarginTop>0.00in</MarginTop>" +
                 "  <MarginLeft>0.00in</MarginLeft>" +
                 "  <MarginRight>0.00in</MarginRight>" +
                 "  <MarginBottom>0.00in</MarginBottom>";
            deviceInfo += "</DeviceInfo>";

            Warning[] warnings;
            m_streams = new List<Stream>();
            report.Render("Image", deviceInfo, CreateStream, out warnings);
            foreach (Stream stream in m_streams)
                stream.Position = 0;
        }
        // Handler for PrintPageEvents
        private void PrintPage(object sender, PrintPageEventArgs ev)
        {
            Metafile pageImage = new
               Metafile(m_streams[_currentPageIndex]);

            //    if (_Rotate)
            //    {
            //       // pageImage.RotateFlip(System.Drawing.RotateFlipType.RotateNoneFlipXY);
            //        ev.Graphics.RotateTransform(-90);
            //    }
            //System.Drawing.Bitmap x = new System.Drawing.Bitmap(m_streams[_currentPageIndex]);
            //x.RotateFlip(System.Drawing.RotateFlipType.Rotate90FlipXY);

            ev.Graphics.DrawImage(pageImage, ev.PageBounds);
            _currentPageIndex++;
            ev.HasMorePages = (_currentPageIndex < m_streams.Count);
        }

        public void Print(string printerName)
        {
            if (m_streams == null || m_streams.Count == 0)
                return;
            PrintDocument printDoc = new PrintDocument();
            printDoc.PrinterSettings.PrinterName = printerName;

            if (!printDoc.PrinterSettings.IsValid)
            {
                string msg = String.Format(
                   "Không tìm thấy máy in \"{0}\".", printerName);
                MessageBox.Show(msg, "Thông báo");
                return;
            }
            printDoc.DefaultPageSettings.Landscape = _Landscape;

            printDoc.PrintPage += new PrintPageEventHandler(PrintPage);
            printDoc.Print();
        }

        public void Print(Form OwnerForm)
        {
            //const string printerName = "SnagIt 8";
            try
            {
                if (m_streams == null || m_streams.Count == 0)
                    return;

                PrintDialog p = new PrintDialog();
                p.AllowSelection = true;
                p.AllowSomePages = true;
                if (p.ShowDialog(OwnerForm) == DialogResult.OK)
                {
                    PrintDocument printDoc = new PrintDocument();

                    printDoc.PrinterSettings = p.PrinterSettings;
                    if (!printDoc.PrinterSettings.IsValid)
                    {
                        string msg = String.Format("Không tìm thấy máy in \"{0}\".", p.PrinterSettings.PrinterName);
                        MessageBox.Show(msg);
                        return;
                    }
                    printDoc.PrintPage += new PrintPageEventHandler(PrintPage);
                    printDoc.Print();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Thông báo");
            }
        }

    }
}
